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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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.03 seconds. Powered By: Snitz Forums 2000