###

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

EmoticonEmoticon