February 14, 2018 at 1:39 pm
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!
February 14, 2018 at 1:43 pm
robert693 - Wednesday, February 14, 2018 1:39 PMHi,
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 14, 2018 at 3:00 pm
Phil Parkin - Wednesday, February 14, 2018 1:43 PMrobert693 - Wednesday, February 14, 2018 1:39 PMHi,
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 15, 2018 at 6:36 am
TheSQLGuru - Wednesday, February 14, 2018 3:00 PMPhil Parkin - Wednesday, February 14, 2018 1:43 PMrobert693 - Wednesday, February 14, 2018 1:39 PMHi,
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 ?
February 15, 2018 at 7:19 am
subramaniam.chandrasekar - Thursday, February 15, 2018 6:36 AMTheSQLGuru - Wednesday, February 14, 2018 3:00 PMPhil Parkin - Wednesday, February 14, 2018 1:43 PMrobert693 - Wednesday, February 14, 2018 1:39 PMHi,
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
February 15, 2018 at 7:20 am
subramaniam.chandrasekar - Thursday, February 15, 2018 6:36 AMTheSQLGuru - Wednesday, February 14, 2018 3:00 PMIn 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 ?
I see no mention of using an unconventional GROUP BY, so I don't understand the reason for your comment.
If the underlying date data types contain time information – and we must assume that they do, based on the requirement – then DATEPART can be used to break these datetimes out into hourly blocks, before grouping them.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 15, 2018 at 7:25 am
The query should have a parameter with a start date and an end date. Arrival Date and Departure date are DateTime fields. Between the start date and the end date, the query should show the number of PATIENT_IDs per hour. So if the record of the PATIENT_ID had an ARRIVAL_DATE of 01/16/2018 04:23:00 am and a DEPARTURE_DATE of 01/18/2018 12:31:00 pm, then in any given hour between 01/16/2018 04:23:00 am and 01/18/2018 12:31:00 pm this PATIENT_ID would count a 1. From the start date to the end date I need,per hour, to find the average number of PATIENT_IDs PRESENT.
February 15, 2018 at 7:31 am
Phil Parkin - Thursday, February 15, 2018 7:20 AMsubramaniam.chandrasekar - Thursday, February 15, 2018 6:36 AMTheSQLGuru - Wednesday, February 14, 2018 3:00 PMIn 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 ?
I see no mention of using an unconventional GROUP BY, so I don't understand the reason for your comment.
If the underlying date data types contain time information – and we must assume that they do, based on the requirement – then DATEPART can be used to break these datetimes out into hourly blocks, before grouping them.
It was actually a wrong ping / comment to your quote, Apologies. thought of replying OP:hehe:
February 15, 2018 at 8:18 am
robert693 - Thursday, February 15, 2018 7:25 AMThe query should have a parameter with a start date and an end date. Arrival Date and Departure date are DateTime fields. Between the start date and the end date, the query should show the number of PATIENT_IDs per hour. So if the record of the PATIENT_ID had an ARRIVAL_DATE of 01/16/2018 04:23:00 am and a DEPARTURE_DATE of 01/18/2018 12:31:00 pm, then in any given hour between 01/16/2018 04:23:00 am and 01/18/2018 12:31:00 pm this PATIENT_ID would count a 1. From the start date to the end date I need,per hour, to find the average number of PATIENT_IDs PRESENT.
OK, so based on the advice you've received so far, what have you tried?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 18, 2018 at 5:26 pm
robert693 - Wednesday, February 14, 2018 1:39 PMHi,
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!
Please see the following article with the understanding that you would apply the method to hours instead of months.
http://www.sqlservercentral.com/articles/T-SQL/105968/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply