| Author |
 Topic  |
|
|
JaybeeSQL
Yak Posting Veteran
77 Posts |
 Posted - 07/04/2012 : 07:24:49
|
Hi all,
The SQL below retrieves value counts between peak hours of 8am and 8pm, but how would I retrieve counts divided by day across 25th June, and 2nd July, and between the same times?
Cheers,
JB
select count(studyuid)
from tblAuditTrail
where command = 16385
and completioncode = 0
and DateStart > '20120630 08:00:00.000' and datestart < '20120630 20:00:00.000'
group by datestart
order by datestart |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/04/2012 : 09:57:21
|
You can change the where clause to this.WHERE command = 16385
AND completioncode = 0
AND DateStart >= '20120625'
AND Datestart < '20120703'
AND datepart(hour,Datestart) >= 8
AND datepart(hour,Datstart) <= 20 |
 |
|
|
JaybeeSQL
Yak Posting Veteran
77 Posts |
Posted - 07/04/2012 : 10:28:04
|
| Thanks Sunita, but that doesn't divide (or rather, group) the counts for each day, it simply gives a total of the whole week. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/04/2012 : 10:30:51
|
quote:
Originally posted by JaybeeSQL
Thanks Sunita, but that doesn't divide (or rather, group) the counts for each day, it simply gives a total of the whole week.
Didn't read your original posting carefully enough.
Change the group by clause to "GROUP BY CAST(DATESTART AS DATE)" if you are on SQL 2008 or "GROUP BY DATEADD(dd,DATEDIFF(dd,0,DATESTART),0)" if you are on SQL 2005 or earlier. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47052 Posts |
Posted - 07/04/2012 : 17:26:13
|
select datestart,count(case when DATEPART(hh,datestart) BETWEEN 8 AND 20 THEN studyuid ELSE NULL END)
from tblAuditTrail
where command = 16385
and completioncode = 0
and DateStart >= '20120625' and datestart < '20120703'
group by datestart
order by datestart
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JaybeeSQL
Yak Posting Veteran
77 Posts |
Posted - 07/05/2012 : 07:01:44
|
| Thank you that lady!! :) |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/05/2012 : 07:33:44
|
| You are very welcome sir :) |
|
|
| |
Topic |
|
Range change
