asked
Best Way to calculate working Days in SQL
I have a sql query that works but it feels very excessive and I feel there must be a much better way of doing this. unfortunately I don't know how to create or use a function to do this or if there is a better way. This is basic version of my query that calculates the number of working days from DateA or DateB to the current Date depending on which is latter. select CASE WHEN DateA< DateB THEN CASE WHEN DATEDIFF(DD,DateB,getdate())+1 - (DATEDIFF(WK,DateB,getdate())*2) - CASE WHEN datename(dw,DateB) ='sunday' then 1 else 0 end >=10 THEN DATEDIFF(DD,DateB,getdate())+1 - (DATEDIFF(WK,DateB,getdate())*2) - CASE WHEN datename(dw,DateB) ='sunday' then 1 else 0 end -10 ELSE 0 END - (select count(*) from BANK_HOLIDAYS where BANK_HOLIDAY >= DateB and BANK_HOLIDAY < getdate()) WHEN DateA >= DateB THEN CASE WHEN DATEDIFF(DD,DateA,getdate()) - (DATEDIFF(WK,DateA,getdate())*2) - CASE WHEN datename(dw,DateA) ='sunday' then 1 else 0 end >=10 THEN DATEDIFF(DD,DateA,getdate()) - (DATEDIFF(WK,DateA,getdate())*2) - CASE WHEN datename(dw,DateA) ='sunday' then 1 else 0 end -10 ELSE 0 END - (select count(*) from BANK_HOLIDAYS where BANK_HOLIDAY >= DateA and BANK_HOLIDAY < getdate()) END AS Number_of_days FROM TableA > Bank Holiday Table format is Bank holiday date,Day, Description(2017-01-01,1,New Years Day)
This is how I would calculate working days. -- calculate number of working days between 2 dates (inclusive) -- working days are Monday - Friday -- less any bank holidays as stored in the bank holiday table declare @DateA date; declare @DateB date; set @DateA = '1 dec 2016' set @DateB = '1 Feb 2017' declare @BankHolidays table (bankholiday date, bankholidaydescription varchar(100)); insert into @BankHolidays select '1 Jan 2017', '2017 New Years Day'; -- set datefirst = 1 : this makes Monday the first day of the week, -- i.e the result of datepart(weekday,...) will be 1 for a Monday -- we do this because it might be different on your server -- we could set it to anything, but we just *need* to know what it is -- do not use names of days, as you might not be using English set datefirst 1; -- create a calendar on the fly starting at the first date with Nbrs_4( n ) as ( select 1 union select 0 ), Nbrs_3( n ) as ( select 1 from Nbrs_4 n1 cross join Nbrs_4 n2 ), Nbrs_2( n ) as ( select 1 from Nbrs_3 n1 cross join Nbrs_3 n2 ), Nbrs_1( n ) as ( select 1 from Nbrs_2 n1 cross join Nbrs_2 n2 ), Nbrs_0( n ) as ( select 1 from Nbrs_1 n1 cross join Nbrs_1 n2 ), Nbrs ( n ) as ( select 1 from Nbrs_0 n1 cross join Nbrs_0 n2 ) , calendar (calendardate, daynum) as (select dateadd(day,N-1,@DateA), datepart(weekday, dateadd(day,N-1,@DateA)) from ( select row_number() over (order by n) from Nbrs ) Tally ( N ) where dateadd(day,N-1,@DateA)
17 People are following this question.
