Я хочу видеть текущие объемы пациентов по дням недели и по часам на основе их зарегистрированной даты начала и даты выписки. Пример: Джон Доу Дата начала: 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
Для счета G111 я хотел бы видеть часы 10, 11,12,13,14 в отдельных строках.
Обычно вы должны ОСТАВИТЬ СОЕДИНЕНИЕ своих данных с таблицей календаря.
В сторону: здорово, что вы привыкли почти использовать терминаторы операторов (;
). Странно, что вы завершили пустой оператор непосредственно перед CTE и не завершили CTE.
@ Mb8787 - Вы сказали: «Как мне получить разбивку объемов по часам, чтобы я мог подсчитать, сколько людей находится в учреждении каждый час, исходя из дат начала и выписки?» ... У меня два вопроса... 1) Вы хотите отслеживать часы, когда нет пациентов и 2) может ли пациент присутствовать более 1 дня?
Если у нас есть метка даты входа и выхода для каждого пациента в другой таблице, мы можем присоединиться к вашей таблице календаря и сгруппировать по часам, чтобы найти идентификаторы присутствующих пациентов и подсчитать их.
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);
Выход:
Номер счета | Дата | Час |
---|---|---|
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 |
Возможно, вам придется настроить <=/</>=/> в зависимости от того, как вы хотите обрабатывать крайние случаи (например, вход или выход прямо в час или вход и выход < 1 часа).
Это отлично работает! Мне нужно было сопоставить типы данных DATETIME и DATE. Как только я это сделал, возвращаемый результат был тем, что я искал. Большое спасибо!
Для быстрого и простого способа (по сравнению с 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 ГБ памяти.
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