How to detrimine date duration, minus weekend days and days on hold

This?

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY [UNIVERSL_ID] ORDER BY LOG_DATESTAMP) AS Seq,*
FROM Reporting_Duration2
)

SELECT c1.[UNIVERSL_ID],
MAX(DATEDIFF(dd,c1.T_MILESTONE_3,COALESCE(c1.T_MILESTONE_8,c1.T_MILESTONE_7))  + 1 - (DATEDIFF(wk,c1.T_MILESTONE_3,COALESCE(c1.T_MILESTONE_8,c1.T_MILESTONE_7)) *2) - CASE WHEN DATEDIFF(dd,0,c1.T_MILESTONE_3)% 7 > 4 THEN 1 ELSE 0 END - CASE WHEN DATEDIFF(dd,0,COALESCE(c1.T_MILESTONE_8,c1.T_MILESTONE_7))% 7 > 4 THEN 1 ELSE 0 END) AS [Duration (Minus Weekends)],
SUM(CASE WHEN c1.OPEN_OH IS NOT NULL AND c2.OH_OPEN IS NOT NULL THEN DATEDIFF(dd,c1.OPEN_OH,c2.OH_OPEN)  + 1 - (DATEDIFF(wk,c1.OPEN_OH,c2.OH_OPEN) *2) - CASE WHEN DATEDIFF(dd,0,c1.OPEN_OH)% 7 > 4 THEN 1 ELSE 0 END - CASE WHEN DATEDIFF(dd,0,c2.OH_OPEN)% 7 > 4 THEN 1 ELSE 0 END
ELSE 0 END) AS [Duration On Hold (minus Weekends)],
(MAX(DATEDIFF(dd,c1.T_MILESTONE_3,COALESCE(c1.T_MILESTONE_8,c1.T_MILESTONE_7))  + 1 - (DATEDIFF(wk,c1.T_MILESTONE_3,COALESCE(c1.T_MILESTONE_8,c1.T_MILESTONE_7)) *2) - CASE WHEN DATEDIFF(dd,0,c1.T_MILESTONE_3)% 7 > 4 THEN 1 ELSE 0 END - CASE WHEN DATEDIFF(dd,0,COALESCE(c1.T_MILESTONE_8,c1.T_MILESTONE_7))% 7 > 4 THEN 1 ELSE 0 END))-(SUM(CASE WHEN c1.OPEN_OH IS NOT NULL AND c2.OH_OPEN IS NOT NULL THEN DATEDIFF(dd,c1.OPEN_OH,c2.OH_OPEN)  + 1 - (DATEDIFF(wk,c1.OPEN_OH,c2.OH_OPEN) *2) - CASE WHEN DATEDIFF(dd,0,c1.OPEN_OH)% 7 > 4 THEN 1 ELSE 0 END - CASE WHEN DATEDIFF(dd,0,c2.OH_OPEN)% 7 > 4 THEN 1 ELSE 0 END
ELSE 0 END)) AS [TotalDays]
FROM CTE c1
INNER JOIN CTE c2
ON c2.[UNIVERSL_ID] = c1.[UNIVERSL_ID]
AND c2.Seq = c1.Seq + 1
GROUP BY c1.[UNIVERSL_ID]

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c