SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Average number of records per hour per datetime range


Average number of records per hour per datetime range

Author
Message
robert693
robert693
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 69
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!
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211064 Visits: 24518
robert693 - Wednesday, February 14, 2018 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!

Sounds straightforward. You'll get far more responses if you script out some sample data, in consumable format.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)

Group: General Forum Members
Points: 120719 Visits: 8986
Phil Parkin - Wednesday, February 14, 2018 1:43 PM
robert693 - Wednesday, February 14, 2018 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!

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
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3436 Visits: 531
TheSQLGuru - Wednesday, February 14, 2018 3:00 PM
Phil Parkin - Wednesday, February 14, 2018 1:43 PM
robert693 - Wednesday, February 14, 2018 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!

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 ?

drew.allen
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62284 Visits: 16614
subramaniam.chandrasekar - Thursday, February 15, 2018 6:36 AM
TheSQLGuru - Wednesday, February 14, 2018 3:00 PM
Phil Parkin - Wednesday, February 14, 2018 1:43 PM
robert693 - Wednesday, February 14, 2018 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!

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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211064 Visits: 24518
subramaniam.chandrasekar - Thursday, February 15, 2018 6:36 AM
TheSQLGuru - Wednesday, February 14, 2018 3:00 PM

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 ?

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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
robert693
robert693
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 69
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.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3436 Visits: 531
Phil Parkin - Thursday, February 15, 2018 7:20 AM
subramaniam.chandrasekar - Thursday, February 15, 2018 6:36 AM
TheSQLGuru - Wednesday, February 14, 2018 3:00 PM

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 ?

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 OPHehe

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211064 Visits: 24518
robert693 - Thursday, February 15, 2018 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.

OK, so based on the advice you've received so far, what have you tried?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859505 Visits: 47085
robert693 - Wednesday, February 14, 2018 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!


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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search