Calculating in SQL the first working day of a given month

Question

Hi! 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.

Answer 1

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

Answer 2

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
    )

Answer 3

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

Answer 4

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.

Answer 5

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
    )

Answer 6

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.