Use CTE to capture volume counts by day and hour

Question

I want to see current patient volumes by days of the week and by hour based off of their registered Start date and Discharge date. Ex: John doe Start date: 01-01-2022 13:00:00 ; End date 01-01-2022 16:25:00

I would like the data to show each Hour John doe is in the Facility. So output would look like something like this:

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

I have my start date and discharge dates in a temp table and thought I could use a CTE to get this done, but not sure how to link the CTE results to my table. How do I get the breakdown of volumes by hour so I can count how many people are in the facility each hour based off of the start and discharge dates?

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)

Sample Data

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

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 — Mb8787, May 04 '22 at 19:49
For acct G111 I would want to see Hours 10, 11,12,13,14 all on separate rows. — Mb8787, May 04 '22 at 19:50
Normally, you would LEFT JOIN your data to the calendar table. — Ross Bush, May 04 '22 at 20:00
Aside: It's great that you're in the habit of almost using statement terminators (`;`). It's odd that you terminated an empty statement just before the CTE and didn't terminate the CTE. — HABO, May 04 '22 at 20:09
@Mb8787 - You said "How do I get the breakdown of volumes by hour so I can count how many people are in the facility each hour based off of the start and discharge dates?" ... I have two questions ... 1) Do you want to track the hours where there are no patients and 2) can a patient be present for more than 1 day? — Jeff Moden, May 05 '22 at 03:10

score 3 · Answer 1 · answered May 04 '22 at 20:01

If we have the enter and leave datestamp for each patient in another table we can join you calendar table and group by hour to find the id's of patients present and count them.

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 rows affected

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

patients | no_pats | Dates                  
:------- | ------: | :----------------------
null     |       0 | 2022-05-01 05:00:00.000
null     |       0 | 2022-05-01 06:00:00.000
null     |       0 | 2022-05-01 07:00:00.000
null     |       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<>fiddle here

Aaron Bertrand · Accepted Answer · 2022-05-04T21:24:14.190

Given this table and sample data:

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

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

Here's how I would do it:

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);

Output:

AccountNumber	Date	Hour
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

Example db<>fiddle

You may need to tweak <=/</>=/> depending on how you want to handle edge cases (e.g. entry or exit right on the hour, or entry and exit < 1 hour).

This works great! I needed to match the DATETIME vs DATE data types. Once I did that the return output was what I was looking for. Greatly appreciated! — Mb8787, May 05 '22 at 14:01

score 1 · Answer 3 · answered May 04 '22 at 22:45

For a fast, simple way (vs CTE) CROSS APPLY using a numbers table or a tally function. In this case I'm using 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
;

This is seems to be faster than the WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), That I have been using for years. It returns 10M rows in 36s on my 32GB laptop. — Ross Bush, May 05 '22 at 02:18

Use CTE to capture volume counts by day and hour

3 Answers3