sql server 08 - query help, display count by hour

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql server 08 - query help, display count by hour

Author

Topic

Ben P
Starting Member

1 Post

Posted - 2012-11-18 : 22:49:23

Hi there,

I'm trying to get an hourly count of each category below for the STATUS column for tomorrow's date.

TABLE:TRIP

ID, STATUS, TRIPDATE
100, Completed, 2012-10-15 00:00:00:000
154 Pending
232 Cancelled
186 Pending
233 Completed
295 Cancelled
151 Cancelled

For example, I'd like to see the output below:

TRIPDATE, STATUS, COUNT
2012-11-19 06:00, COMPLETED, 45,
2012-11-19 07:00 PENDING 30
2012-11-19 08:00 CANCELLED 20

I tried using the datepart function with the syntax below but
not getting a count breakdown by hour - only get output with one field.
select datepart(hh,tripdate), count (*)
from trip
group by datepart(hh,tripdate)

I can currently obtain the daily count by using syntax below but need hourly count.

select status, count(id) as 'count' from trip where tripdate = convert(varchar(8), getdate()+1,1)group by status

Thanks in advance!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-11-19 : 00:44:34

Try something like this..

Select CONVERT(datetime,convert (varchar(20),GETDATE(),101) +' '+ convert(varchar(5),v.number)+':00:00')date_hour
,status,COUNT(status)
from master..spt_values v
inner join TRIP t
on v.CONVERT(datetime,convert (varchar(20),GETDATE(),101) +' '+ convert(varchar(5),v.number)+':00:00') = t.TRIPDATE
where v.type='p' and v.number <=23
Group by TRIPDATE,status

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 01:24:14

[code]
SELECT DATEADD(hh,DATEDIFF(hh,0,TRIPDATE),0) AS HourDt, STATUS,COUNT(*) AS Cnt
FROM Table
GROUP BY DATEADD(hh,DATEDIFF(hh,0,TRIPDATE),0), STATUS
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources