SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql server 08 - query help, display count by hour
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ben P
Starting Member

1 Posts

Posted - 11/18/2012 :  22:49:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 11/19/2012 :  00:44:34  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/19/2012 :  01:24:14  Show Profile  Reply with Quote

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


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000