SQL Query - Hourly Count

Question

SQL Query - Hourly Count

uknites

SSC Veteran

Points: 255
More actions
July 10, 2018 at 6:40 am

#354556

Hi
I am not a DBA so please excuse me if this question is very basic
I want a report to know hourly count SUM number students who applied for admission between two dates.
I have a column called [TIME_SUBMITTED] in the table [ABC].[dbo].[TBL_ADMISSION]
What would be the query for this purpose?
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

uknites - Tuesday, July 10, 2018 6:40 AM
Hi
I am not a DBA so please excuse me if this question is very basic
I want a report to know hourly count SUM number students who applied for admission between two dates.
I have a column called [TIME_SUBMITTED] in the table [ABC].[dbo].[TBL_ADMISSION]
What would be the query for this purpose?
thanks

You would really want to post the DDL, sample data and expected results for queries. There is usually just too many unknowns without that information. In your case, I'm not sure what the hourly count sum means. It's okay if it's not explained correctly - that's why the expected results help in understanding what you are looking. Some examples on how to do this are in this article:
Forum Etiquette: How to post data/code on a forum to get the best help

A basic query along the lines of what you are trying would be something like:
SELECT DATEPART(hh, TIME_SUBMITTED) as 'Hour', COUNT(*) as 'Count' FROM TBL_ADMISSION WHERE TIME_SUBMITTED > Date1 and TIME_SUBMITTED < Date2 GROUP BY DATEPART(hh, TIME_SUBMITTED)

It just gives you the count by hours between two dates. However, it's likely it may not be what you are looking for as we would need the DDL, sample data and expected results. Things like what if there were 10 during the 1:00 pm hour one day and 12 for the next day? Is that separate or combined? Do you need the date also? Is that what you mean by sum of the counts?

Sue

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 2

You'll need to group by dates as well, since 1400 occurs on multiple days. If you group by the hour, you'll combine the counts from Monday and Tuesday

drew.allen SSC Guru Points: 76984 More actions · Answer 3

Also, it helps to know if you want to include hours where there is no data. That would require a different approach to fill in the hours missing data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 4

Great point, Drew. In that case, you might want to build a calendar table of hours and join back. I might even want to do this from the start, since a common enhancement would be to show me all the hours of the day and counts. That's often easier for humans. If you need to strip out the empty hours, that's easy.

Actually, Drew, that would be a great little article. Take some data, break by hours, show all hours and counts. If you want to write it, that would be great. If not, I'll post in the articles requested section.

drew.allen SSC Guru Points: 76984 More actions · Answer 5

Steve Jones - SSC Editor - Tuesday, July 10, 2018 9:44 AM
Great point, Drew. In that case, you might want to build a calendar table of hours and join back. I might even want to do this from the start, since a common enhancement would be to show me all the hours of the day and counts. That's often easier for humans. If you need to strip out the empty hours, that's easy.
Actually, Drew, that would be a great little article. Take some data, break by hours, show all hours and counts. If you want to write it, that would be great. If not, I'll post in the articles requested section.

Sure, I can write that article.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 736105

July 10, 2018 at 1:32 pm

#1997057

Excellent. Looking forward to it.

uknites SSC Veteran Points: 255 More actions · Answer 7

thanks a lot every one
i wrote this
SELECT [TIME_SUBMITTED] "Time Submitted",
COUNT(*) "No of Students"
FROM [ABC].[dbo].[TBL_ADMISSION]
where TIME_SUBMITTED BETWEEN '7/1/2018' AND '7/8/2018'
GROUP BY [TIME_SUBMITTED]
ORDER BY [TIME_SUBMITTED];

But its not counting the number of students who applied for admission in one hour
can we write something to check count after 59 mins , sum it and show and output as

Date and first min of hour | count
thanks

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 8

You haven't grouped by hour, but by time.

Please read the discussion and look at the answers, you need to group by hour.

drew.allen SSC Guru Points: 76984 More actions · Answer 9

Instead of having to enter the same formula multiple times, I used the CROSS APPLY to create a field that I could use instead.

I also changed your query to use a half-closed interval rather than a closed interval.

SELECT Hour_Submitted AS [Time Submitted], COUNT(*) [No of Students] FROM [ABC].[dbo].[TBL_ADMISSION] CROSS APPLY (VALUES(DATEADD(HOUR, DATEDIFF(HOUR, '2010-01-01', TIME_SUBMITTED), '2010-01-01'))) hr(Hour_Submitted) where Hour_Submitted >= '7/1/2018' AND Hour_Submitted < '7/8/2018' GROUP BY [Hour_Submitted] ORDER BY [Hour_Submitted];

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

uknites SSC Veteran Points: 255 More actions · Answer 10

thanks a lot drew
it showed exactly what i was looking for 🙂
Appreciated 🙂