Log in  ::  Register  ::  Not logged in

 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: Friday, November 08, 2013 6:38 AM Points: 1,077, Visits: 174
 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
 SSChampion Group: General Forum Members Last Login: 2 days ago @ 2:23 PM Points: 10,854, Visits: 10,012
 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 03, 2013 6:52 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:43 AM Points: 34,537, Visits: 28,702