Count the number of hours between two dates

Question

Count the number of hours between two dates

jkbarr30

Grasshopper

Points: 16
More actions
April 28, 2016 at 1:35 pm

#310202

I work in an Emergency Department and I'm looking for some help with a query that will count the number of patients waiting in the ED at each hour. So if a patient comes in at 1PM and isn't seen until 3PM I'd like the results to show the hour and the total patients waiting. In the example I listed it would be like this for just this one patient that waited 2 hours.
Hour|Patients
1 | 1
2 | 1
My goal is to show at each hour of the day any given patient that is in the waiting room and what hour they are here. This is my query that counts the number of patients that arrives at each hour:
SELECT CAST(dt_ARRIVAL AS DATE) [Date],
DATEPART(HOUR,dt_ARRIVAL) [Hour], Count(1) [Patients]
FROM Table
WHERE dt_ARRIVAL >= '2016-04-28'
GROUP BY CAST(dt_ARRIVAL AS DATE), DATEPART(HOUR,dt_ARRIVAL)
ORDER BY 1 ,2
The date/time of their arrival to the waiting room is 'dt_ARRIVAL' and the date/time they move from the waiting room is 'dt_INIT_BED'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Please post some sample data (no need for real data) and expected results based on that data. To know how this is needed read the articles in my signature.

This is so we can understand exactly what you need and be able to test the code before posting.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 2

Luis beat me to the sample data speech...but that being said I would advise not to use ordinal position in your ORDER BY clause. It's a bad habit to get into.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

yb751 (4/28/2016)
Luis beat me to the sample data speech...but that being said I would advise not to use ordinal position in your ORDER BY clause. It's a bad habit to get into.

As an alternative (and my preferred option) use column alias.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63436 More actions · Answer 4

(Makes me think this place needs a "canned response macro"... )

😀

Bill Talada SSChampion Points: 11954 More actions · Answer 5

The "date" datatype does not contain time.

I think you want something like this:

DECLARE @h TABLE (Hr int not null);

INSERT into @h values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);

SELECT * FROM @h;

DECLARE @t TABLE (dt_arrival datetime not null, dt_init_bed datetime null);

INSERT into @t values('20160428 00:22','20160428 00:22');

INSERT into @t values('20160428 05:02','20160428 14:22');

INSERT into @t values('20160428 05:15','20160428 16:22');

SELECT

h.hr,

(

SELECT COUNT(*)

FROM @t t

WHERE DATEPART(HOUR, dt_arrival) <= h.Hr

AND DATEPART(HOUR, dt_init_bed) > h.Hr

) AS waitcount

FROM @h h

;

Sergiy SSC Guru Points: 110207 More actions · Answer 6

jkbarr30 (4/28/2016)
... and what hour they are here

Can you define this please?

Arrived on 8:50 and placed in bed on 9:10 - it's obviously within 1st hour.

But arrived on 8:10 and placed in bed by 9:50 - what hour would be that?

_____________
Code for TallyGenerator

jkbarr30 Grasshopper Points: 16 More actions · Answer 7

I would like it to round to the nearest hour if possible. I'm working on properly submitting data for everyone to see. Thank you for your replies so far and not being rude to someone new to the forum.