时间戳

首页 > 解决方案 > SQL 显示每天的每一小时

sql - SQL 显示每天的每一小时

问题描述

我编写了以下代码，将显示患者每天到达和离开的数据分解为每天每小时的患者普查。

该代码有效，但是对于每个日期，而不是在 0-23 小时中每个添加一个小时，它为 0 添加第二行，因此它每天分为 25 行而不是 24 行。我很确定问题出在某个地方在下面的交叉应用中，但我包含了其余代码供您参考。

我非常感谢您提供的任何帮助。另外，如果您对如何在此处发布代码并使其看起来更正常有任何提示，请告诉我。谢谢！

--Create my temporary table 
SELECT *
INTO #Temporary
FROM dbo.Census
WHERE YEAR(startdatetime) >= 2018
ORDER BY
    startdatetime
    ,pt_id

--Use the Cross Apply to split out every day into every hour 
SELECT
    Date = CAST(D AS DATE)
    ,Hour = DATEPART(HOUR, D)
    ,pt_id
    ,cendate
    ,locationid
    ,[room-bed]
    ,startdatetime
    ,enddatetime
    ,minutes
    ,DayOfWeek
    ,WeekInt
    ,MyStartMinutes = 0
    ,MyEndMinutes = 0
INTO #Temporary2
FROM #Temporary A
CROSS APPLY
(
    SELECT TOP ( ABS(DATEDIFF(HOUR, A.startdatetime, A.enddatetime) + 1))
        D = DATEADD(HOUR, -1 + ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )), A.startdatetime)
    FROM master..spt_values n1
        ,master..spt_values n2
) B

--Update values for MyStartMinutes and MyEndMinutes 
UPDATE #Temporary2
SET MyStartMinutes = CASE WHEN ( DATEPART(HOUR, startdatetime) = Hour )
                              THEN DATEPART(mi, enddatetime)
                         ELSE 0 END

UPDATE #Temporary2
SET MyEndMinutes = CASE WHEN ( DATEPART(HOUR, enddatetime) = Hour )
                            AND DATEDIFF(DAY, enddatetime, cendate) = 0
                            THEN DATEPART(mi, enddatetime)
                       ELSE 0 END

--Update values of startdatetime and enddatetime 
UPDATE #Temporary2
SET startdatetime = DATEADD(HOUR, Hour, DATEADD(MINUTE, MyStartMinutes, CAST(CAST(startdatetime AS DATE) AS DATETIME)))

UPDATE #Temporary2
SET enddatetime = CASE WHEN ( Hour < 23 )
                           THEN ( DATEADD(HOUR, Hour + 1, DATEADD(MINUTE, MyEndMinutes, CAST(CAST(startdatetime AS DATE) AS DATETIME))))
                      WHEN Hour = 23
                          THEN ( DATEADD(HOUR, 0, DATEADD(MINUTE, MyEndMinutes, CAST(CAST(enddatetime AS DATE) AS DATETIME))))
                      ELSE '' END

--Update Value of Minutes 
UPDATE #Temporary2
SET Minutes = DATEDIFF(mi, startdatetime, enddatetime)

SELECT * 
FROM #Temporary2 
ORDER BY minutes DESC

以下是来自的示例数据dbo.Census：

org pt_id cendate   location bed    startdate      enddate           minutes DOW 
A   5     1/8/2018    7E     50    1/8/2018 8:00    1/9/2018 0:00    960     Mon 
A   5     1/9/2018    7E     50    1/9/2018 0:00    1/10/2018 0:00   1440    Tue 
A   5     1/10/2018   7E     50    1/10/2018 0:00   1/11/2018 0:00   1440    Wed 
A   5     1/11/2018   7E     50    1/11/2018 0:00   1/11/2018 14:00  840     Thu 
A   1     10/17/2016  ED     10    10/17/2016 1:05  10/17/2016 10:21 556     Mon 
A   2     5/10/2017   4L     20    5/10/2017 15:09  5/11/2017 0:00   531     Wed 
A   3     5/14/2017   4L     30    5/14/2017 0:00   5/14/2017 8:12   492     Sun 
A   4     6/3/2017    5C     40    6/3/2017 0:00    6/4/2017 0:00    1440    Sat

标签： sqltsql

解决方案

我认为你是正确的，你CROSS APPLY是这里的罪魁祸首。在我自己的示例数据上测试了您的代码后，我发现如果其中有单独的记录在dbo.Census它们的开始日期和结束日期之间有重叠的天数，那么这些日期和小时数会被重复，具体取决于它们共享的记录数和天数。

所以我所做的就是将 PK 添加dbo.Census到 中CROSS APPLY，然后在子查询中使用该 id 列将结果过滤到只有那些 id 匹配的结果。这是我更改的代码部分：

SELECT
    Date = CAST(D AS DATE)
    ,Hour = DATEPART(HOUR, D)
    ,A.pt_id
    ,cendate
    ,locationid
    ,[room-bed]
    ,startdatetime
    ,enddatetime
    ,minutes
    ,DayOfWeek
    ,WeekInt
    ,MyStartMinutes = 0
    ,MyEndMinutes = 0
INTO #Temporary2
FROM #Temporary A
CROSS APPLY
(
    SELECT TOP ( ABS(DATEDIFF(HOUR, A.startdatetime, A.enddatetime) + 1))
        D = DATEADD(HOUR, -1 + ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )), A.startdatetime)
        ,A.pt_id
    FROM master..spt_values n1
        ,master..spt_values n2
) B
WHERE A.pt_id = B.pt_id

我假设它pt_id是 的主键dbo.Census。如果不是这种情况，您只需替换pt_id为dbo.Census.

推荐阅读