如何将PatientName与7天内的小时数相加(如果超过9小时)

Question

当他们在7天内有9个小时或更长的时间时，我必须找到PatientName。然后通过PatientName提供小时之和。示例：

TempTable1获得了一年的数据：

PatientName       VisitDate       Hours
Steve             11/2/2016        1
Steve             11/3/2016        1
Pete              11/1/2016        3
Pete              11/2/2016        7
Dave              11/1/2016        3
Dave              11/2/2016        3
Dave              2/2/2016         3

史蒂夫没有足够的时间。戴夫有9个小时，但不在7天之内。皮特在7天内有10个小时。因此，产出应类似于：

   PatientName          (SUM)Hours
   Pete                    10

这完全超出了我的能力。我认为这将需要一个多方面的选择和领先和滞后，但这完全超出了我的能力水平。

希望SQL忍者神灵能做到这一点。请救救我。

Answer 1

一种方法是自连接和聚合：

select t1.patientname, sum(t2.hours)
from temptable1 t1 join
     temptable1 t2
     on t2.patientname = t1.patientname and
        t2.visitdate >= t1.visitdate and
        t2.visitdate < datead(day, 7, t1.visitdate)
group by t1.patientname
having sum(t2.hours) >= 9;

Answer 2

下面的查询帮助您获得所需的输出：

select patient_name,week,sum(hours) as hours_served from
(
select patient_name,date,hours,week(date) as week
from temptable1
)
group by patient_name,week
having hours_served >=9;

Answer 3

CREATE TABLE #Temp
(
    PatientName VARCHAR(32),
    VisitDate   DATE,
    [Hours]     INT
)

INSERT #Temp
VALUES

('Steve', CAST('11/2/2016' AS DATE), 1),
('Steve', CAST('11/3/2016' AS DATE), 1),
('Pete', CAST('11/1/2016' AS DATE), 3),
('Pete', CAST('11/2/2016' AS DATE), 7),
('Dave', CAST('11/1/2016' AS DATE), 3),
('Dave', CAST('11/2/2016' AS DATE), 3),
('Dave', CAST('2/2/2016' AS DATE), 3)

SELECT p.PatientName, MAX(p.HoursSum)
FROM (
    SELECT t2.PatientName, SUM(t2.[Hours]) AS HoursSum
    FROM #Temp t1
        JOIN #Temp t2 ON t1.PatientName = t2.PatientName AND t2.VisitDate BETWEEN t1.VisitDate AND DATEADD(DAY, 7, t1.VisitDate)
    GROUP BY t2.PatientName, t1.VisitDate
    HAVING SUM(t2.[Hours]) >= 9
) p
GROUP BY p.PatientName

DROP TABLE #Temp