Range change - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Range change

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-04 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 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
Posting Yak Master

112 Posts

Posted - 2012-07-04 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 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

52326 Posts

Posted - 2012-07-04 : 17:26:13

[code]
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
[/code]

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

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-05 : 07:01:44

Thank you that lady!! :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 07:33:44

You are very welcome sir :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources