| Author |
 Topic  |
|
|
Ben P
Starting Member
1 Posts |
 Posted - 11/18/2012 : 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
Aged Yak Warrior
India
997 Posts |
Posted - 11/19/2012 : 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
India
47069 Posts |
Posted - 11/19/2012 : 01:24:14
|
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/
|
|
|
| |
Topic |
|
|
|
sql server 08 - query help, display count by hour
