如何在SQL-server-2008-r2中对打开的记录进行计数，按小时和日期分组

Question

我在Microsoft SQL Server r2中有医院患者入院数据，如下所示：

PatientID, AdmitDate,        DischargeDate
Jones.     1-jan-13 01:37.   1-jan-13 17:45
Smith      1-jan-13 02:12.   2-jan-13 02:14
Brooks.    4-jan-13 13:54.   5-jan-13 06:14

我想一天一小时地数一下医院里的病人人数

1-jan-13 00:00. 0
1-jan-13 01:00. 0
1-jan-13 02:00. 1
1-jan-13 03:00. 2

我需要在结果中包括没有病人入院的时间。

我不能创建表格，所以创建一个列出所有小时和日期的参考表是不可行的。

有什么建议吗？

Answer 1

要解决这个问题，您需要一个日期-小时列表。下面是从与表交叉联接的24小时录取日期中获得的。24小时表是从information_schema.columns计算的--这是在SQL Server中获取小数字序列的技巧。

其余的只是这个表和小时数之间的连接。这个版本是按小时计算病人人数的，因此，例如，在同一小时内入院和出院的人不算在内。一般来说，一些人直到他们入院后的下一个小时才会被计算在内：

with dh as (
     select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour
     from (select distinct cast(cast(AdmitDate as DATE) as datetime) as thedatehour
           from Admission a
          ) a cross join
          (select ROW_NUMBER() over (order by (select NULL)) as seqnum
           from INFORMATION_SCHEMA.COLUMNS
          ) hours
          where hours <= 24
    )
select dh.DateHour, COUNT(*) as NumPatients
from dh join
     Admissions a
     on dh.DateHour between a.AdmitDate and a.DischargeDate
group by dh.DateHour
order by 1

这也假设每天都有招生。这似乎是一个合理的假设。如果没有，日程表将是一个很大的帮助。

Answer 2

下面是一种(丑陋的)方法：

;WITH DayHours AS
(
    SELECT 0 DayHour
    UNION ALL
    SELECT DayHour+1
    FROM DayHours
    WHERE DayHour+1 <= 23
)
SELECT B.AdmitDate, A.DayHour, COUNT(DISTINCT PatientID) Patients
FROM DayHours A
CROSS JOIN (SELECT DISTINCT CONVERT(DATE,AdmitDate) AdmitDate
            FROM YourTable) B
LEFT JOIN YourTable C
    ON B.AdmitDate = CONVERT(DATE,C.AdmitDate)
    AND A.DayHour = DATEPART(HOUR,C.AdmitDate)
GROUP BY B.AdmitDate, A.DayHour

Answer 3

这有点乱七八糟，其中包含一个临时表，其中包含您提供的测试数据，但是

    CREATE TABLE #HospitalPatientData (PatientId NVARCHAR(MAX), AdmitDate DATETIME, DischargeDate DATETIME)
INSERT INTO #HospitalPatientData
SELECT 'Jones.',     '1-jan-13 01:37:00.000',   '1-jan-13 17:45:00.000' UNION
SELECT 'Smith',      '1-jan-13 02:12:00.000',   '2-jan-13 02:14:00.000' UNION
SELECT 'Brooks.',    '4-jan-13 13:54:00.000',  '5-jan-13 06:14:00.000'

;WITH DayHours AS
(
    SELECT 0 DayHour
    UNION ALL
    SELECT DayHour+1
    FROM DayHours
    WHERE DayHour+1 <= 23
),
HospitalPatientData AS
(
SELECT CONVERT(nvarchar(max),AdmitDate,103) as AdmitDate ,DATEPART(hour,(AdmitDate)) as     AdmitHour, COUNT(PatientID) as CountOfPatients
FROM #HospitalPatientData
GROUP BY CONVERT(nvarchar(max),AdmitDate,103), DATEPART(hour,(AdmitDate))
),
Results AS
(
SELECT MAX(h.AdmitDate) as Date, d.DayHour
FROM HospitalPatientData h
INNER JOIN DayHours d ON d.DayHour=d.DayHour
GROUP BY AdmitDate, CountOfPatients, DayHour
)

SELECT r.*, COUNT(h.PatientId) as CountOfPatients
FROM Results r
LEFT JOIN #HospitalPatientData h ON CONVERT(nvarchar(max),AdmitDate,103)=r.Date AND     DATEPART(HOUR,h.AdmitDate)=r.DayHour
GROUP BY r.Date, r.DayHour
ORDER BY r.Date, r.DayHour

DROP TABLE #HospitalPatientData