Microsoft SQL Server计算连续事件之间的总时间

Question

第一篇文章在这里，但任何人都可以引导或帮助我了解以下问题。对于下表中的“Patient_Table”，我如何找出patient_id 22生病的总天数。

ID Patient_ID  Listing_Number  Date    Status 
----------------------------------------------------------------- 
1  22    1     01/01/2016  Healthy 
2  22    2     01/11/2016  Sick 
3  34    1     01/13/2016  Healthy 
4  22    3     01/20/2016  Healthy 
5  22    4     01/22/2016  Sick 
6  22    5     01/23/2016  Healthy 

下面是我的逻辑到目前为止，但我不知道正确的语法。

declare 
@count  int = 1, 
@Days_sicks int = 0 

while @count <= (select max (Listing_number) from Patient_Table where Patient_id = '22') 

begin 
    case 
    when (select status from Patient_Table where Patient_id = '22' and Listing_number = @count) = 'Sick' 
    then @Days_sicks = @Days_sicks + datediff('dd', (select min date from Patient_Table where patient_id = 22 and listing_number > @count and status != 'Sick'), (select date from patient_table where patient_id = 22 and listing_number = @count) 
    else @Days_sicks 
    end as Days_sicks 

set @Count = @Count + 1 
END; 

---

我也试过这一个，但它不工作得很好，我对条款有问题，跟团

SELECT t1.patient_id, 
    DATEDIFF(dd, 
     t1.date, 
     (SELECT MIN(t3.date) 
     FROM Patient_Table t3 
     WHERE t3.patient_id = t1.patient_id 
     AND t3.date> t1.date) as Days_sicks 
    ) 
FROM Patient_Table t1 
WHERE EXISTS(
    SELECT 'NEXT' 
    FROM Patient_Table t2 
    WHERE t2.patient_id = t1.patient_id 
    AND t2.date> t1.date 
    AND t2.status != 'sick') 
    and t1.patient_id = '22' 

所需的结果

Patient id Days_sicks 
22   10 

Answer 1

使用lead()功能，然后聚合：

select patient_id, 
     sum(datediff(day, date, coalesce(next_date, getdate()))) 
from (select pt.*, 
      lead(date) over (partition by patient_id order by date) as next_date 
     from patient_table pt 
    ) pt 
where status = 'Sick' 
group by patient_id; 

注意：如果某人现在生病了，那么这会使用当前日期来结束“生病”状态。

此外，如果患者多次就诊时生病，这将起作用。

Answer 2

Select s.Patient_ID, 
    sum(datediff(day, s.Date, coalesce(h.date, getdate()) totalDaysSick 
From table s left join table h 
    on h.Patient_ID = s.Patient_ID 
    and h.Status = 'Healthy' 
    and s.Status ='Sick' 
    and h.Date = (Select Min(date) from table 
        where Patient_ID = s.Patient_ID 
        and Date > s.Date) 
Group By Patient_Id 

Answer 3

您可以按日期使用Lag窗函数和订单，然后做一个DATEDIFF找到当前和以前的日期之间的天数。然后做一个天数的总和：

select 
    sickdays.Patient_ID 
    ,Sum(sickdays.DayNums) as SickDays 
from 
    (select 
      Patient_ID 
      ,Datediff(day, Lag([Date], 1, null) over (order by [Date]), [Date]) as DayNums 

     from Patient 
     where Patient_ID = 22 
     and Status = 'Sick' 
     group by Patient_ID 
        ,Date) as sickdays 
where 
    sickdays.DayNums is not null 
group by sickdays.Patient_ID