Task 4: Query for weekends extraction

• Basically, it's a query to extract the weekends in order to use only the business days. It seems a pretty easy task but after many test cases I realized that it could be difficult when not all the attention is focused on below case conditions. So, be carefully on that tightrope :)

declare @day1 datetime
declare @day2 datetime
declare @BusinessDays int

SELECT TOP 1

             @day1 = ExecStartDate ,

             @day2 = getDate()

FROM Audit

WHERE PkgName = 'MASTER PACKAGE'

ORDER BY ExecStartDate DESC

set datefirst 1  --Monday

set @BusinessDays = ( select ( datediff ( dd, @day1, @day2 )) - 

- (( datepart ( wk, @day2 ) - datepart ( wk, @day1 )) * 2 ) +

+ ( case when datepart ( dw, @day1 ) = 7 then 1 else 0 end ) -

 ( case when datepart ( dw, @day2 ) = 7 then 1 else 0 end )  as BusinessDays )