How to calculate the number of days between two dates and total the number of days for multiple dates

Question

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.

Here is an example of the report:

Case        Person         Number of Uncleared Days
12          Tony Camp             18
25          Everly Mo             4

I arrived at the result by calculating the days between each of the Uncleared and Cleared status and then adding the days. This will give me a total for the total number of days between each Uncleared and Cleared date for each person.

Exp. I got the difference between 9/4/17 and 9/7/17 and then the difference between 9/9/17 and 9/25/17. Then I added the days to get a total number of Uncleared days.

Answer 1

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

For SQL2000:

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