使用CTE按天和小時捕獲體積計數-有解無憂

我想根據他們注冊的開始日期和出院日期按一周中的幾天和每小時查看當前的患者數量。例如：John doe 開始日期：01-01-2022 13:00:00 ；結束日期 01-01-2022 16:25:00

我希望資料顯示 John doe 在設施中的每一小時。所以輸出看起來像這樣：

John Doe  01-01-2022     ( Hour) 13
John Doe  01-01-2022     ( Hour) 14
John Doe  01-01-2022     ( Hour) 15
John Doe  01-01-2022     ( Hour) 16

我在臨時表中有我的開始日期和出院日期，并認為我可以使用 CTE 來完成這項作業，但不確定如何將 CTE 結果鏈接到我的表。我如何按小時細分數量，以便根據開始和出院日期計算每小時有多少人在設施中？

DECLARE @minDateTime AS DATETIME;
DECLARE @maxDateTime AS DATETIME;

SET @minDateTime = '2022-05-01 05:28:05.000';
SET @maxDateTime = '2022-05-02 06:50:00.000';

;
WITH Dates_CTE
     AS 
     (SELECT @minDateTime AS Dates
         UNION ALL
         SELECT Dateadd(hh, 1, Dates)
         FROM   Dates_CTE
         WHERE  Dates < Dateadd(hh, -1, @maxDateTime)
         )

SELECT --Convert(VARCHAR,Year,Dates)
                        Dates
                        ,Year(Dates) as 'Year'
                        ,Month(Dates) as 'Month'
                        ,Day(Dates) as 'day'
                        ,Datename(DW,Dates) as 'DayName'
                        ,DATEPART(HOUR,Dates) as 'hh'

FROM   Dates_CTE
OPTION (MAXRECURSION 0)

樣本資料

AccountNumber   ServiceDateTime             RegistrationTypeDischargeDateTime 
G111            2021-05-07 10:44:19.000     2021-05-07 14:30:00.000 
G222            2021-05-08 09:59:00.000     2021-05-08 10:56:00.000 
G333            2021-07-02 11:35:07.000     2021-07-02 11:53:00.000 
G444            2021-07-07 07:57:16.000     2021-07-07 13:35:00.000

uj5u.com熱心網友回復：

如果我們在另一個表中有每個患者的進入和離開日期戳，我們可以加入您的日歷表并按小時分組以查找在場患者的 ID 并計算他們。

create table inTreatment(
patientid int,
enter datetime,
leave datetime
);
insert into inTreatment values
(1,'2022-05-01 09:00:00','2022-05-01 18:00:00'),
(2,'2022-05-01 11:00:00','2022-05-01 14:00:00'),
(3,'2022-05-01 12:00:00','2022-05-02 15:00:00')
GO

3 行受影響

DECLARE @minDateTime AS DATETIME;
DECLARE @maxDateTime AS DATETIME;

SET @minDateTime = '2022-05-01 05:00:00.000';
SET @maxDateTime = '2022-05-02 06:00:00.000';

;
WITH Dates_CTE
     AS 
     (SELECT @minDateTime AS Dates
         UNION ALL
         SELECT Dateadd(hh, 1, Dates)
         FROM   Dates_CTE
         WHERE  Dates < Dateadd(hh, -1, @maxDateTime)
         )

SELECT --Convert(VARCHAR,Year,Dates)
                        string_agg(patientid,',') patients,
                        count(patientid) no_pats,
                         Dates
                        --,Year(Dates) as 'Year'
                        --,Month(Dates) as 'Month'
                        --,Day(Dates) as 'day'
                        ----,Datename(DW,Dates) as 'DayName'
                        --,DATEPART(HOUR,Dates) as 'hh'

FROM   Dates_CTE d
left join InTreatment i
on enter <= Dates and leave >= Dates
group by dates

OPTION (MAXRECURSION 0)
GO

患者 | no_pats | 日期                  
:------- | ------: | :------------------------
空     | 0 | 2022-05-01 05:00:00.000
空     | 0 | 2022-05-01 06:00:00.000
空     | 0 | 2022-05-01 07:00:00.000
空     | 0 | 2022-05-01 08:00:00.000
1 | 1 | 2022-05-01 09:00:00.000
1 | 1 | 2022-05-01 10:00:00.000
1,2 | 2 | 2022-05-01 11:00:00.000
1,2,3 | 3 | 2022-05-01 12:00:00.000
1,2,3 | 3 | 2022-05-01 13:00:00.000
1,2,3 | 3 | 2022-05-01 14:00:00.000
1,3 | 2 | 2022-05-01 15:00:00.000
1,3 | 2 | 2022-05-01 16:00:00.000
1,3 | 2 | 2022-05-01 17:00:00.000
1,3 | 2 | 2022-05-01 18:00:00.000
3 | 1 | 2022-05-01 19:00:00.000
3 | 1 | 2022-05-01 20:00:00.000
3 | 1 | 2022-05-01 21:00:00.000
3 | 1 | 2022-05-01 22:00:00.000
3 | 1 | 2022-05-01 23:00:00.000
3 | 1 | 2022-05-02 00:00:00.000
3 | 1 | 2022-05-02 01:00:00.000
3 | 1 | 2022-05-02 02:00:00.000
3 | 1 | 2022-05-02 03:00:00.000
3 | 1 | 2022-05-02 04:00:00.000
3 | 1 | 2022-05-02 05:00:00.000

db<>在這里擺弄

uj5u.com熱心網友回復：

鑒于此表和示例資料：

CREATE TABLE dbo.Admissions
(
  AccountNumber char(4),
  ServiceDateTime datetime,
  RegistrationTypeDischargeDateTime datetime
);

INSERT dbo.Admissions VALUES
('G111','20210507 10:44:19','20210507 14:30:00');

這是我的做法：

DECLARE @min datetime = '20210507 05:28:05',
        @max datetime = '20210508 06:50:00';
        
DECLARE @d tinyint = DATEDIFF(HOUR, @min, @max),
        @floor datetime = SMALLDATETIMEFROMPARTS
        (YEAR(@min), MONTH(@min), DAY(@min), DATEPART(HOUR, @min), 0);

; -- see sqlblog.org/cte
WITH hours(h) AS
(
  SELECT @floor UNION ALL 
  SELECT DATEADD(HOUR, 1, h) 
  FROM hours WHERE h <= @max
)
SELECT a.AccountNumber, Date = CONVERT(date, hours.h), 
  Hour = DATEPART(HOUR, hours.h)
FROM hours INNER JOIN dbo.Admissions AS a
  ON a.ServiceDateTime < DATEADD(HOUR, 1, hours.h)
  AND a.RegistrationTypeDischargeDateTime >= hours.h
OPTION (MAXRECURSION 32767);

輸出：

帳號	日期	小時
G111	2021-05-07	10
G111	2021-05-07	11
G111	2021-05-07	12
G111	2021-05-07	13
G111	2021-05-07	14

示例db<>fiddle

您可能需要調整<=/</>=/>，具體取決于您希望如何處理邊緣情況（例如，準時進入或退出，或進入和退出< 1 小時）。

uj5u.com熱心網友回復：

對于使用數字表或計數功能的快速、簡單的方法（與 CTE 相比）CROSS APPLY。在這種情況下，我使用dbo.fnTally

select a.AccountNumber, cast(a.ServiceDateTime as date) [Date],
       datepart(hour, dateadd(hour, fn.N, cast(a.ServiceDateTime as time))) hr
from #Admissions a
     cross apply dbo.fnTally(0, datediff(hour, 
                                         a.ServiceDateTime,
                                         a.RegistrationTypeDischargeDateTime)) fn;

dbo.fnTally


CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/474592.html

標籤：sql sql服务器 tsql 公共表表达式

上一篇：從SQLWindow函式中洗掉重復項

下一篇：如何從文本中洗掉某些前導字符