Counting the # of patients in a waiting room every hour 1

[sbbrown9924]

Please help me get organized around a query I need to write. I need to measure waiting room times by every hour in the day in an ER. Currently there is an archive database of all patient encounters and within the record of a patient encounter there is the TriageTime and InRoomTime. The interval between these two timestamps is the time a patient spends waiting in the waiting room (right out of the department of redundancy department!).

I know how to determine that basic interval ( datediff('n',TriageTime, InRoomTime) ) but for the rest I'm clueless.

What I'd like to be able to do is take the entire database and for every hour, take a snapshot of how many people are waiting and what the average and max waiting times are for that hour.

So the output would be:

...
Time # of Patients Average Wait Longest Wait
6am 10,545 45min 2:30
7am 12,444 47min 2:45
8am 15,376 1:05 3:07
...
...


Thanks.

 

[Melagan]

Here's a starting point:

SELECT Format([TriageTime],"h ampm") AS Increment
, Count(([TriageTime])) AS [#OfPatients]
, Avg(DateDiff("n",[TriageTime],[InRoomTime])) AS AvgWait
, Max(DateDiff("n",[TriageTime],[InRoomTime]) & " minutes") AS MaxWait

FROM tblYourTable

GROUP BY Format([TriageTime],"hh ampm"), Format([TriageTime],"h ampm")

ORDER BY Format([TriageTime],"hh ampm");

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Actually, the concatentation messes up the agregate functions. This as an alternative SQL, then use the concatenations in a report =)

SELECT Format([TriageTime],"h ampm") AS Increment
, Count(([TriageTime])) AS [#OfPatients]
, Avg(DateDiff("n",[TriageTime],[InRoomTime])) AS AvgWait
, Max(DateDiff("n",[TriageTime],[InRoomTime])) AS MaxWait

FROM tblYourTable

GROUP BY Format([TriageTime],"h ampm"), Format([TriageTime],"hh ampm")

ORDER BY Format([TriageTime],"hh ampm");

With the example table below, I got the following results further below:

ID PatientID TriageTime InRoomTime
3 101 6:05:00 AM 6:25:00 AM
4 102 6:50:00 AM 6:59:00 AM
5 103 7:20:00 AM 8:35:00 AM
6 104 7:10:00 AM 7:55:00 AM
7 105 11:15:00 AM 11:35:00 AM
8 106 8:06:00 AM 8:25:00 AM
9 107 9:20:00 AM 9:23:00 AM
10 108 9:32:00 AM 9:55:00 AM
11 109 9:40:00 AM 9:57:00 AM
12 110 10:02:00 AM 10:30:00 AM
________

Increment #OfPatients AvgWait MaxWait
6 AM 2 14.5 20
7 AM 2 60 75
8 AM 1 19 19
9 AM 3 14.33333333 23
10 AM 2 24 28
11 AM 1 20 20


~Melagan
______
"It's never too late to become what you might have been.

 

[sbbrown9924]

Melagan;

This is looking good.

Where can I find information about using "hh ampm". Is that vbscript?

Also, I put an ORDER BY Format([TriageTime],'hh')so that it would list hours sequentially from 12am to 11pm.

Thanks!

 

[sbbrown9924]

more questions:

1.) count(([TriageTime]))

What's the reason for double parentheses?

2.) How does sql interpret this query to only count TriageTimes for each hour interval?

3.) Instead of counting the total number of patients in each hour interval, how could this query determine the average number of patients in each hour interval. This will be important when I add start date and end date parameters.

Thanks for your help.

 

[Eupher]

sbbrown9924,

The "h ampm" and "hh ampm" are arguments of the Format function - they determine how the returned data is displayed. Do a search in the VBA help files on the Format function, it will explain all the available format abbreviations.

No purpose at all for the double parentheses. Normally we see this when we use the Access query design grid to build SQL statements. It's a really handy way to write SQL to use in your code (although there are some syntax differences that must be accounted for); but the query builder goes way overboard with parentheses.

It's the Count statement and GROUP BY statements in conjuction with the Format function that cause the triage times to be counted for each hour. In more-or-less plain English, the query says "Count all the patients, then format the triage time to display in hourly increments, then group the count of the patients within the hourly increments."

Given some time I could figure out an answer for your last question - but SQL is not my strength, so I will leave it to one of the query experts here to address that.

Does that help?

Ken S.

 

[sbbrown9924]

Yes it does, thanks for 'splainin this to me!