Hi,
I have a DB with PATIENT_ID, ARRIVAL_DATE, DEPARTURE_DATE, and DEPARTMENT. I need to find the average number of patients in each department per hour over a time frame. So if the date range chosen is 01/01/2018, and the end date chosen is 02/01/2018, then I need to know the average number of patients that were in each department in hours 1:00 to 23:00 over this time period. I have tried several ways but I cannot get this to work Any help would be greatly appreciated!
Sounds straightforward. You'll get far more responses if you script out some sample data, in consumable format.
In the meantime, checkout DATEPART in books online. That and GROUP BY will be in your solution I think.
Can't we use normal conventional Group by here ?
Yes, but you'll need some way to determine the groups and DATEPART is one method of determining the groups. I would actually use a slightly different method which essentially rounds down to the nearest whole hour.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA