EMBEDDED DATEDIFF EXCLUD WEEKENDS + CASE STATEMENT

Question

I have the below query that utilizes a case statement. I would like to datediff two dates but exclude weekend days. I have the below that excutes but now I would like to exclude Sat and Sunday from this... AND DATEDIFF(DD,A.ALERTS_CREATE_DT,S.CreatedDate) <= 2

CASE WHEN 
S.Name IN ('Assessment','Survey') 
  AND A.ALERT_DESC = 'ER'       
  AND CAST(A.ALERTS_CREATE_DT AS DATE) <= CAST(S.CreatedDate AS DATE) 
AND DATEDIFF(DD,A.ALERTS_CREATE_DT,S.CreatedDate) <= 2 /*EXCLUDE Sat and Sunday from the calculation*/   

Full Query

SELECT 

  CASE WHEN 
    S.Name IN ('Assessment','Survey') 
      AND A.ALERT_DESC = 'ER'       
      AND CAST(A.ALERTS_CREATE_DT AS DATE) <= CAST(S.CreatedDate AS DATE)
      AND  
      ( DATEDIFF(DD,A.ALERTS_CREATE_DT,S.CreatedDate) <= 2 /*Business Days*/   
      --DATEDIFF(DD,A.ALERTS_CREATE_DT,S.CreatedDate) + 1
      ---(DATEDIFF(WK,A.ALERTS_CREATE_DT,S.CreatedDate) * 2) 
      ---(CASE WHEN DATENAME(DW,A.ALERTS_CREATE_DT) = 'SUNDAY' THEN 1 ELSE 0 END)
      ---(CASE WHEN DATENAME(DW,S.CreatedDate) = 'SATURADAY' THEN 1 ELSE 0 END)
               )

   THEN 'Y'
    WHEN A.ALERT_DESC = 'model' OR S.CreatedDate IS NULL OR S.Name = 'ER'
    THEN ''
     ELSE 'N'
      END 'Count towards Alerts'

FROM A
FULL  S ON A.id= S.id

WHERE 1=1

Answer 1

This should give you the required result by excluding the Saturdays and Sundays.

SELECT A.ALERT_DESC,A.ALERTS_CREATE_DT,S.Name,S.CreatedDate,
  CASE WHEN 
      S.Name IN ('Assessment','Survey')  AND A.ALERT_DESC = 'ER'       
      AND CAST(A.ALERTS_CREATE_DT AS DATE) <= CAST(S.CreatedDate AS DATE)
      AND  
        (( DATEDIFF(DD,A.ALERTS_CREATE_DT,S.CreatedDate)+1 
        - (datediff(wk,A.ALERTS_CREATE_DT,S.CreatedDate)*2) 
        - case when datepart(dw,A.ALERTS_CREATE_DT)=1 then 1 else 0 end 
        - case when datepart(dw,S.CreatedDate)=7 then 1 else 0 end
         )) <=2  THEN 'Y'
       WHEN A.ALERT_DESC = 'model' OR S.CreatedDate IS NULL OR S.Name = 'ER' THEN ''
       ELSE 'N' END 'Count towards Alerts'
FROM A,S