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.
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.