tnbumbray tnbumbray - 18 days ago 1561
SQL Question

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

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 Source

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