Hello,
I am trying to look at the number of people in the department at a snapshot (every hour of the day)
My ultimate aim is then produced a heat map that shows days and times when the department is at its busiest.
I have produced the below query (and have repeating up to 23:00).
The difficulty I have is capturing people in the department at Midnight
e.g. when someone arrives in the department at 21:59 and don't leave until the following morning 00:32 - how can I count this person in the '22:00', '23:00' and '00:00' snapshot?
Any help would be appreciated!
SELECT
Count([EncounterID]) Number
,[ArrivalDate]
,Cast (Cast ([ArrivalDate] as DateTime) + '01:00:00' as DateTime) [Snapshot Time]
,'01:00:00' [Snapshot]
FROM CDS_Common.dbo.vwAandE AS AE
Where '01:00:00' Between ArrivalTime and DepartureTime
And ArrivalDate BETWEEN '30/05/2016' AND '12/06/16'
Group By [ArrivalDate]
Union All
SELECT
Count([EncounterID]) Number
,[ArrivalDate]
,Cast (Cast ([ArrivalDate] as DateTime) + '02:00:00' as DateTime) [Snapshot Time]
,'02:00:00' [Snapshot]
FROM CDS_Common.dbo.vwAandE AS AE
Where '02:00:00' Between ArrivalTime and DepartureTime
And ArrivalDate BETWEEN '30/05/2016' AND '12/06/16'
Group By [ArrivalDate]
Union All