# question

## 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)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.