Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Tsql to calculate workdays - holidays: for current month Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, June 19, 2013 12:23 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 4, 2014 1:52 PM Points: 1,077, Visits: 199
 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
Post #1465324
 Posted Wednesday, June 19, 2013 12:39 PM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:44 PM Points: 16,134, Visits: 16,835
 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1465335
 Posted Saturday, August 3, 2013 6:52 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:59 PM Points: 42,046, Visits: 39,432