Calculation in SQL of the first working day of this month

Question

I need to calculate all the invoices that were paid on the first "N" days of the month. I have two tables

. INVOICE: contains account information. The only field that matters is called "datePayment"

. HOLYDAYS: This is a single column table. The entries in this table are in the form "2009-01-01", 2009-05-01 ", etc.

I should also consider Saturday and Sunday (this may not be a problem, because I could insert these days in the Hollidays table to treat them as hollidays, if necessary)

The problem is to calculate which is the "limit of payment".

select count(*) from invoice 
where datePayment  < PAYMENTLIMIT

My question is how to calculate this PAYMENT LINE. Where PAYMENTLIMIT is the "fifth working day of every month."

The query must be executed in Mysql and Oracle, so standard SQL must be used.

Any clues?

EDIT To match the name of the question, the pseudo-query should be read as follows:

select count(*) from invoice 
where datePayment  < FIRST_WORKING_DAY + N

then the question can be reduced to calculate FIRST_WORKING_DAY of each month.

Answer 1

You can find the first date in the month where the date is not in the holiday table and the date is not a holiday:

select min(datePayment), datepart(mm, datePayment)
from invoices
where datepart(dw, datePayment) not in (1,7) --day of week
and not exists (select holiday from holidays where holiday = datePayment)
group by datepart(mm, datePayment) --monthnr

Answer 2

Something like this might work:

create function dbo.GetFirstWorkdayOfMonth(@Year INT, @Month INT)
returns DATETIME
as begin
    declare @firstOfMonth VARCHAR(20)
    SET @firstOfMonth = CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR) + '-01'

    declare @currDate DATETIME 
    set @currDate = CAST(@firstOfMonth as DATETIME)

    declare @weekday INT
    set @weekday = DATEPART(weekday, @currdate)

    -- 7 = saturday, 1 = sunday
    while @weekday = 1 OR @weekday = 7
    begin
        set @currDate = DATEADD(DAY, 1, @currDate)
        set @weekday = DATEPART(weekday, @currdate)
    end

    return @currdate
end

I am not 100% sure whether the "day of the week" numbers are fixed or may depend on your locale on your SQL Server. Check this!

Mark

Answer 3

, , : , - ( 11K). , is_weekday, , , . julianized_date. , first_working_day_this_month, ( SQL !), "" .

Answer 4

( ), "" , ( -sql):

select min(ourdates.datevalue)
from ourdates
where ourdates.year=<given year> and ourdates.month=<given month>
    and ourdates.isworkday
    and not exists (
        select * from holidays
        where holidays.datevalue=ourdates.datevalue
    )

Answer 5

, UDF . TestDate, ​​.

DECLARE @Month INT
DECLARE @Year INT

SELECT @Month = 5
SELECT @Year = 2009

DECLARE @FirstDate DATETIME
SELECT @FirstDate = CONVERT(varchar(4), @Year) + '-' + CONVERT(varchar(2), @Month) + '-' + '01 00:00:00.000'

DROP TABLE #HOLIDAYS
CREATE TABLE #HOLIDAYS (HOLIDAY DateTime)

INSERT INTO #HOLIDAYS VALUES('2009-01-01 00:00:00.000')
INSERT INTO #HOLIDAYS VALUES('2009-05-01 00:00:00.000')

DECLARE @DateFound BIT
SELECT @DateFound = 0
WHILE(@DateFound = 0)
BEGIN
    IF(
        DATEPART(dw, @FirstDate) = 1
        OR
        DATEPART(dw, @FirstDate) = 1
        OR
        EXISTS(SELECT * FROM #HOLIDAYS WHERE HOLIDAY = @FirstDate)
    )
    BEGIN
        SET @FirstDate = DATEADD(dd, 1, @FirstDate)
    END
    ELSE
    BEGIN
        SET @DateFound = 1
    END
END

SELECT @FirstDate

, , , , . ( , ). , , 00:00:00. , , 1- , . .

Answer 6

N 2009 :

select * from invoices as x
where 
    datePayment between '2009-01-01' and '2009-12-31'


    and exists
    ( 
        select             
                 1
        from invoices
        where
            -- exclude holidays and sunday saturday...
            (
                datepart(dw, datePayment) not in (1,7) -- day of week


                /*
                -- Postgresql and Oracle have programmer-friendly IN clause
                and 
                (datepart(yyyy,datePayment), datepart(mm,datePayment))
                not in (select hyear, hday from holidays) 
                */


                -- this is the MSSQL equivalent of programmer-friendly IN
                and 
                not exists
                (
                    select * from holidays
                    where 
                        hyear = datepart(yyyy,datePayment)
                        and hmonth = datepart(mm, datePayment)
                )                                
            )
            -- ...exclude holidays and sunday saturday



            -- get the month of x datePayment
            and                 
            (datepart(yyyy, datePayment) = datepart(yyyy, x.datePayment)
             and datepart(mm, datePayment) = datepart(mm, x.datePayment)) 


        group by 
            datepart(yyyy, datePayment), datepart(mm, datePayment)    

        having 
            x.datePayment < MIN(datePayment) + @N -- up to N working days
    )

Answer 7

SELECT DATEADD(
    WEEK,
    DATEDIFF( --x weeks between 1900-01-01 (Monday) and inner result
        WEEK,
        0, --1900-01-01
        DATEADD( --inner result
            DAY,
            6 - DATEPART(DAY, GETDATE()),
            GETDATE()
        )
    ),
    0 --1900-01-01 (Monday)
)

Answer 8

SELECT DATEADD(day, DATEDIFF (day, 0, DATEADD (month, DATEDIFF (month, 0, GETDATE()), 0)  -1)/7*7 + 7, 0);

Answer 9

select if(weekday('yyyy-mm-01') < 5,'yyyy-mm-01',if(weekday('yyyy-mm-02') < 5,'yyyy-mm-02','yyyy-mm-03'))

- 5, 6, , .