April 13, 2008 at 8:06 pm
Hi folks,
I have a scheduled task to be run at every hour, I wrote a query to get the result:
select convert(varchar(17), auditdate),
count(1) from dailyscore
group by convert(varchar(17), auditdate)
order by convert(varchar(17), auditdate)
Apr 13 2008 1:00256
Apr 13 2008 1:0121
Apr 13 2008 1:046
Apr 13 2008 2:00271
Apr 13 2008 2:0123
Apr 13 2008 2:046
Apr 13 2008 3:00284
Apr 13 2008 3:0226
Apr 13 2008 3:056
Apr 13 2008 4:00296
Apr 13 2008 4:0229
Apr 13 2008 4:0510
Apr 13 2008 5:00302
Apr 13 2008 5:017
Apr 13 2008 5:0229
However, this is really not exact what I want. Because I need the result group by each hour, but as you can see, the task can not be finished in one minute, which means my query can't group the result based on hour. What I want is result like:
Apr 13 2008 1:00285 =256(1:00) + 21(1:01) + 6(1:04)
Apr 13 2008 2:00nnn
Apr 13 2008 3:00nnn
Apr 13 2008 4:00nnn
Apr 13 2008 5:00nnn
.......
Thanks. Any clue is appreciated.
April 13, 2008 at 8:42 pm
I already got it:
select convert(varchar(13),auditdate,120)+':00',
count(1) from dailyscore
group by convert(varchar(13),auditdate,120)
order by convert(varchar(13),auditdate,120)
April 14, 2008 at 6:14 am
Hi ,
Run this script hope you find out a solution to your problem :-
create table Acc_count
(
Acc_row_no int identity(1,1),
acc_dt datetime ,
acc_count int
)
Go
insert into Acc_count(acc_dt,acc_count)
select convert(datetime ,'apr 13 2008 1:00') dt , 256 cnt union all
select convert(datetime ,'apr 13 2008 1:01') dt , 21 cnt union all
select convert(datetime ,'apr 13 2008 1:04') dt , 6 cnt union all
select convert(datetime ,'apr 13 2008 2:00') dt , 271 cnt union all
select convert(datetime ,'apr 13 2008 2:01') dt , 23 cnt union all
select convert(datetime ,'apr 13 2008 2:04') dt , 6 cnt union all
select convert(datetime ,'apr 13 2008 3:00') dt , 284 cnt union all
select convert(datetime ,'apr 13 2008 3:02') dt , 26 cnt union all
select convert(datetime ,'apr 13 2008 3:05') dt , 6 cnt union all
select convert(datetime ,'apr 13 2008 4:00') dt , 296 cnt union all
select convert(datetime ,'apr 13 2008 4:02') dt , 29 cnt union all
select convert(datetime ,'apr 13 2008 4:05') dt , 10 cnt union all
select convert(datetime ,'apr 13 2008 5:00') dt , 302 cnt union all
select convert(datetime ,'apr 13 2008 5:01') dt , 7 cnt union all
select convert(datetime ,'apr 13 2008 5:02') dt , 29 cnt
Go
select convert(varchar , acc_dt,101) + ' ' + convert(varchar,datepart(hh,acc_dt)) + ':00' dt , sum(acc_count) total_cnt from Acc_count
group by convert(varchar , acc_dt,101) + ' ' + convert(varchar,datepart(hh,acc_dt))
Thanks ,
Amit Gupta
(MCP)
April 14, 2008 at 6:22 am
Or...
select dateadd(hour, datediff(hour, 0, acc_dt), 0) as dt, sum(acc_count) as total_cnt
from Acc_count group by dateadd(hour, datediff(hour, 0, acc_dt), 0)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply