tnbumbray - 3 years ago 2033
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.

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download