Как рассчитать количество дней между двумя датами и общее количество дней для нескольких дат

Question

Мне нужно создать запрос, который будет вычислять количество дней между очищенными и неочищенными датами, а затем вычислять общее количество этих дней.

Вот пример набора данных:

 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

Я пришел к результату, рассчитав дни между каждым статусом "Неочищенный" и "Очищенный", а затем сложив дни. Это даст мне общее количество дней между каждой необработанной и очищенной датой для каждого человека.

Exp. Я получил разницу между 9/9/17 и 9/7/17, а затем разницу между 9/9/17 и 25.09.17. Затем я добавил дни, чтобы получить общее количество неочищенных дней.

Answer 1

Вы можете использовать это.

;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

Для 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