Tutorial :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.


Solution: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  


Solution: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


Solution: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.


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


Solution: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.


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


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


Solution:8

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


Solution: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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »