Expression needed for last working day of the month

Question

Expression needed for last working day of the month

david5515

SSChasing Mays

Points: 638
More actions
October 21, 2015 at 4:20 am

#399215

Hi guys,
I need to create a report to run on the last day of the working month
So essentially on the 30th October, I will need the report to capture everything before 30th October
On the last working day of the month in November, the report will need to capture everything from 30th October-29th November
Hopefully this is achievable.
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63517 More actions · Answer 1

The easiest way would probably be to use a calendar table and mark each holiday. Then you could just exclude those from your MAX query. Lynn Pettis posted a bunch of date functions here[/url]. You could use one of them with your calendar table and be off to the races.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

david5515 (10/21/2015)
Hi guys,
I need to create a report to run on the last day of the working month
So essentially on the 30th October, I will need the report to capture everything before 30th October
On the last working day of the month in November, the report will need to capture everything from 30th October-29th November
Hopefully this is achievable.
Thanks in advance

For date math, Lynn has an excellent article of common date functions published at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. If you're filtering on a datetime data type, you'll want to make sure you allow for the time value as well.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

pietlinden (10/21/2015)
The easiest way would probably be to use a calendar table and mark each holiday. Then you could just exclude those from your MAX query. Lynn Pettis posted a bunch of date functions here[/url]. You could use one of them with your calendar table and be off to the races.

Excellent point about holidays. The calendar table does seem like a logical choice.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 4

Hi David

Small example for current date:

=FormatDateTime(IIf(DatePart(DateInterval.Weekday,DateAdd(DateInterval.Day,-(Day(Now())),DateAdd(DateInterval.Month,1,Now()))) = 7,DateAdd(DateInterval.Day,-(Day(Now())+1),DateAdd(DateInterval.Month,1,Now())),DateAdd(DateInterval.Day,-(Day(Now())+1),IIf(DatePart(DateInterval.Weekday,DateAdd(DateInterval.Day,-(Day(Now())),DateAdd(DateInterval.Month,1,Now()))) = 1,DateAdd(DateInterval.Day,-(Day(Now())+1),DateAdd(DateInterval.Month,1,Now())),DateAdd(DateInterval.Day,-(Day(Now())+2),DateAdd(DateInterval.Month,1,Now()))))),DateFormat.ShortDate)

But it doesn't check Church & Civic Holidays, personally I prefer scalar function to find it.

Best regards

Mike

Ps. Sunday specified as the first day of the week in this example.