Calculating in SQL the first working day of a given month

Question

I have to calculate all the invoices which have been paid in the first 'N' days of a month. I have two tables

. INVOICE: it has the invoice information. The only field which does matter is called 'datePayment'

. HOLYDAYS: It is a one column table. Entries at this table are of the form "2009-01-01", 2009-05-01" and so on.

I should consider also Saturdays and Sundays (this might be not a problem because I could insert those days at the Hollidays table in order to consider them as hollidays if neccesary)

The problem is to calculate which is the 'payment limit'.

select count(*) from invoice 
where datePayment  < PAYMENTLIMIT

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

The query should be run under Mysql and Oracle therefore standard SQL should be used.

Any hint?

EDIT In order to be consistent with the title of the question the pseudo-query should the read as follows:

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

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

Sheeting

The correct spelling of your 2nd table is "holiday" – Mosley

In fact the table is called 'Feiertag'. :-) Anyway, thanks for the comment. will correct it. – Sheeting

I am having trouble understanding what your question is. Do you want to know how to find the first working day for each month or what the payment limit is? Is payment limit the first working day + 5 more working days? – Torr

Answer 1

You could look for the first date in a month, where the date is not in the holiday table and the date is not a weekend:

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

Determinate

or the other way round...pre-calculate all first working days in a month and store them in a table. so you only have to query this table – Deneendenegation

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'm not 100% sure about whether the "weekday" numbers are fixed or might depend on your locale on your SQL Server. Check it out!

Marc

Conradconrade

Answer 3

Rather than a Holidays table of days to exclude, we use the calendar table approach: one row for every day the application will ever need (thirty years spans a modest 11K rows). So not only does it have an is_weekday column, it has other things relevant to the enterprise e.g. julianized_date. This way, every possible date would have a ready-prepared value for first_working_day_this_month and finding it involves a simple lookup (which SQL products tend to be optimized for!) rather than 'calculating' it each time on the fly.

Redmund

Answer 4

We have dates table in our application (filled with all dates and date parts for some tens of years), what allows various "missing" date manipulations, like (in pseudo-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
    )

Aguiar

Answer 5

Ok, at a first stab, you could put the following code into a UDF and pass in the Year and Month as variables. It can then return TestDate which is the first working day of the month.

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

The things I don`t like with this solution though are, if your holidays table contains all days of the month there will be an infinite loop. (You could check the loop is still looking at the right month) It relies upon the dates being equal, eg all at time 00:00:00. Finally, the way I calculate the 1st of the month past in using string concatenation was a short cut. There are much better ways of finding the actual first day of the month.

Republicanize

Answer 6

Gets the first N working days of each month of year 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
    )

Kappel

Answer 7

Returns the first Monday of the current month

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)
)

Odilia

Answer 8

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

Contrariety

Answer 9

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

Saturdays and Sundays are 5, 6 so you only need two checks to get the first working day

Romance