Hi,
I have a DB with PATIENT_ID, ARRIVAL_DATE, DEPARTURE_DATE, and DEPARTMENT. I need to find the average number of patients in each department per hour over a time frame. So if the date range chosen is 01/01/2018, and the end date chosen is 02/01/2018, then I need to know the average number of patients that were in each department in hours 1:00 to 23:00 over this time period. I have tried several ways but I cannot get this to work Any help would be greatly appreciated!
Usable sample data would be extremely helpful here.
Can an ARRIVAL_DATE and DEPARTURE_DATE exceed total time of 24 hours?
Hi
I tried to create sample data first
Does this look good to you
Thanks
Please let me know
Create Sample Date
drop table harish_temp
go
create table harish_temp
(
PATIENT_ID INT NULL,
ARRIVAL_DATE DATETIME NULL,
DEPARTURE_DATE DATETIME NULL,
DEPARTMENT INT NULL
)
go
insert into harish_temp select 1,'2012-10-09 10:30','2012-10-09 11:30',10
insert into harish_temp select 2,'2012-10-09 13:10','2012-10-09 15:26',10
insert into harish_temp select 3,'2012-10-09 15:35','2012-10-09 15:45',20
insert into harish_temp select 4,'2012-10-09 08:06','2012-10-09 09:10',20
insert into harish_temp select 5,'2012-10-09 16:01','2012-10-09 18:12',30
insert into harish_temp select 6,'2012-10-09 14:28','2012-10-09 19:26',30
insert into harish_temp select 6,'2012-10-09 08:30','2012-10-09 11:30',10
insert into harish_temp select 7,'2012-10-09 14:10','2012-10-09 15:26',10
insert into harish_temp select 8,'2012-10-09 09:35','2012-10-09 15:45',20
insert into harish_temp select 9,'2012-10-09 08:06','2012-10-09 09:10',20
insert into harish_temp select 10,'2012-10-09 13:01','2012-10-09 18:12',30
insert into harish_temp select 11,'2012-10-09 12:28','2012-10-09 19:26',30
go