I need to create a query that will calculate the number of days between the cleared dates and uncleared dates and then calculate the total of those days.
Here is an example of the data set:
Name Status Date Explanation
Tony Camp Uncleared 9/4/17 Need more information.
Tony Camp Cleared 9/7/17 Paper work signed
Tony Camp Uncleared 9/9/17 Placement is full.
Tony Camp Cleared 9/25/17 Placement is ready.
Everly Mo Uncleared 9/26/17 Not ready.
Everly Mo Cleared 10/01/17 Ready.
Stan Mann Uncleared 10/01/17 Not Ready.
Case Person Number of Uncleared Days
12 Tony Camp 18
25 Everly Mo 4
You can use this.
;WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY Name, Status ORDER BY [Date]) FROM DataSet ) SELECT T1.Name Person, SUM(DATEDIFF(DAY,T1.Date, T2.Date)) -1 [Number of Uncleared Days] FROM CTE T1 INNER JOIN CTE T2 ON T1.Name = T2.Name AND T2.Status ='Cleared' AND T1.RN = T2.RN WHERE T1.Status ='Uncleared' GROUP BY T1.Name
SELECT Person, SUM([Date_Diff]) - 1 AS [Number of Uncleared Days] FROM ( SELECT D1.Name AS Person, DATEDIFF(DAY, [Date] , (SELECT TOP 1 [Date] FROM DataSet D2 WHERE D2.Status ='Cleared' AND D1.Name = D2.Name AND D1.Date < D2.Date) ) AS [Date_Diff] FROM DataSet AS D1 WHERE D1.Status ='Uncleared' ) AS SubQ WHERE [Date_Diff] IS NOT NULL GROUP BY Person