Tsql to calculate workdays - holidays: for current month

Question

Post reply

Tsql to calculate workdays - holidays: for current month

David Smerchek

Ten Centuries

Points: 1297
More actions
June 19, 2013 at 12:23 pm

#276480

SELECT DATEDIFF (day, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110))
- (2 * DATEDIFF(week, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110)))
- CASE WHEN DATEPART(weekday, DATEADD(d,-(DAY(getdate())-1),getdate()) + @@DATEFIRST) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, GETDATE() + @@DATEFIRST) = 1 THEN 1 ELSE 0 END
-count(
Case
when '01/01/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1
when '7/4/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1
when '9/2/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1
when '11/28/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1
when '12/25/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1
end) as WorkDays

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Is there a question here or are you sharing something?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

David Smerchek (6/19/2013)
SELECT DATEDIFF (day, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110))
- (2 * DATEDIFF(week, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110)))
- CASE WHEN DATEPART(weekday, DATEADD(d,-(DAY(getdate())-1),getdate()) + @@DATEFIRST) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, GETDATE() + @@DATEFIRST) = 1 THEN 1 ELSE 0 END
-count(
Case
when '01/01/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1
when '7/4/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1
when '9/2/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1
when '11/28/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1
when '12/25/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1
end) as WorkDays

The problem with such code is that it uses hardcoded dates that must be updated every year. If you forget, you can really mess up a whole bunch of things. Using a Calendar table would likely be a much better solution.

Whatever solution you use, at least add some error handling for when you run out of dates or have exceeded hardcoded expectations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)