Number present during a time period 1

[Doc94]

I am looking for some expertise in writing a query. This is from a large Emergency Department database written in access 2000, front and back end......
I have a patient ID (unique), arrival time and discharge time. What I need to do is count the number of peole in the department each hour. I can tally the arrivals and discharges but not a tally of where they overlap, not just when they arrive or leave.
Below is the SQL for tally of arrivals by hour

SELECT Tally.Beg, Tally.End, Clock.ClockTime, Count(Tally.Sunday) AS CountOfSunday, Count(Tally.Monday) AS CountOfMonday, Count(Tally.Tuesday) AS CountOfTuesday, Count(Tally.Wednesday) AS CountOfWednesday, Count(Tally.Thursday) AS CountOfThursday, Count(Tally.Friday) AS CountOfFriday, Count(Tally.Saturday) AS CountOfSaturday, [CountOfSunday]+[CountOfMonday]+[CountOfTuesday]+[CountOfWednesday]+[CountOfThursday]+[CountOfFriday]+[CountOfSaturday] AS Total
FROM Tally RIGHT JOIN Clock ON Tally.Time = Clock.Number
GROUP BY Tally.Beg, Tally.End, Clock.ClockTime;

Any hel would be greatly appreciated and I can provide more info if nee be.

 

[Doc94]

To further explain:
Patient 1 arrives at 10 am and leaves at 2pm
Patient 2 arrives at 11 am and leaves at 1pm
Patient 3 arrives at 11 am and leaves at 4:30pm.
A chart of this would look like this

Time 10 11 12 1 2 3 4 5
Pt. 1 Yes Yes Yes Yes No No No No
Pt. 2 No Yes Yes No No No NO No
Pt 3 No Yes Yes Yes Yes Yes Yes No

Total 1 3 3 2 1 1 1 0
in Department

 

[Golom]

Consider SQL of the following sort

Select I.Num As [TheHour], Count(*) As [In The Ward]

From Integers As I, Patients As P

Where Hour([ArrivalTime]) <= Num AND Hour([DepartTime]) => Num
  AND I.Num < 25

Group By I.Num

Where "Integers" is a table of the form
[tt]Integers
Num
1
2
3
:
24
[/tt]


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Doc94]

Golom-
I have tried but can not get this to work. I already have a table clock which should be the same as the integers table. I have a large "detail" table with most of the other data in it including hospital arrival time and disposition time. I have tried substituting the "detail" table for the "P" table and the "clock" table for the integer table but it does not work. Any further advise would be helpful.
J

 

[PHV]

You tried something like this ?
SELECT Clock.ClockTime, Count(Tally.Sunday) AS CountOfSunday, Count(Tally.Monday) AS CountOfMonday, Count(Tally.Tuesday) AS CountOfTuesday, Count(Tally.Wednesday) AS CountOfWednesday, Count(Tally.Thursday) AS CountOfThursday, Count(Tally.Friday) AS CountOfFriday, Count(Tally.Saturday) AS CountOfSaturday, [CountOfSunday]+[CountOfMonday]+[CountOfTuesday]+[CountOfWednesday]+[CountOfThursday]+[CountOfFriday]+[CountOfSaturday] AS Total
FROM Tally, Clock
WHERE Clock.ClockTime BETWEEN Tally.Beg And Tally.End
GROUP BY Clock.ClockTime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886