SQLTeam.com | Weblogs | Forums

Midnight snapshot


#1

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


#2

Without the table schema definition I'm guessing at some details. I am assuming that ArrivalDate is a Date data type and ArrivalTime and DepartureTime are Time data types. My suggestion would be to only use DateTime for Arrival and Departure. Then a person is present during the snapshot if it falls between ArrivalDateTime and DepartureDateTime.
You may need to account for the fact that there isn't a DepartureDateTime (since they are still present). You can COALESCE the DepartureDateTime to some point in the future.