Используйте CTE для регистрации объемов по дням и часам

Я хочу видеть текущие объемы пациентов по дням недели и по часам на основе их зарегистрированной даты начала и даты выписки. Пример: Джон Доу Дата начала: 01-01-2022 13:00:00 ; Дата окончания 01-01-2022 16:25:00

Я хотел бы, чтобы данные показывали каждый час, когда Джон находится на объекте. Таким образом, вывод будет выглядеть примерно так:

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

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-0 7-2021 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 04.05.2022 21:49

Для счета G111 я хотел бы видеть часы 10, 11,12,13,14 в отдельных строках.

Mb8787 04.05.2022 21:50

Обычно вы должны ОСТАВИТЬ СОЕДИНЕНИЕ своих данных с таблицей календаря.

Ross Bush 04.05.2022 22:00

В сторону: здорово, что вы привыкли почти использовать терминаторы операторов (;). Странно, что вы завершили пустой оператор непосредственно перед CTE и не завершили CTE.

HABO 04.05.2022 22:09

@ Mb8787 - Вы сказали: «Как мне получить разбивку объемов по часам, чтобы я мог подсчитать, сколько людей находится в учреждении каждый час, исходя из дат начала и выписки?» ... У меня два вопроса... 1) Вы хотите отслеживать часы, когда нет пациентов и 2) может ли пациент присутствовать более 1 дня?

Jeff Moden 05.05.2022 05:10
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
5
97
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Если у нас есть метка даты входа и выхода для каждого пациента в другой таблице, мы можем присоединиться к вашей таблице календаря и сгруппировать по часам, чтобы найти идентификаторы присутствующих пациентов и подсчитать их.

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

дб <> рабочий пример здесь

Ответ принят как подходящий

Учитывая эту таблицу и примеры данных:

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

Выход:

Номер счетаДатаЧас
G1112021-05-0710
G1112021-05-0711
G1112021-05-0712
G1112021-05-0713
G1112021-05-0714

Возможно, вам придется настроить <=/</>=/> в зависимости от того, как вы хотите обрабатывать крайние случаи (например, вход или выход прямо в час или вход и выход < 1 часа).

Это отлично работает! Мне нужно было сопоставить типы данных DATETIME и DATE. Как только я это сделал, возвращаемый результат был тем, что я искал. Большое спасибо!

Mb8787 05.05.2022 16:01

Для быстрого и простого способа (по сравнению с 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
;

Это кажется быстрее, чем WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1) ),(1),(1))dt(n)), которым я пользуюсь годами. Он возвращает 10 миллионов строк за 36 секунд на моем ноутбуке с 32 ГБ памяти.

Ross Bush 05.05.2022 04:18

Другие вопросы по теме