Count within certain parameter

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Count within certain parameter

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2010-02-15 : 12:01:43

I have a report and I want to count appointments that have between 3 seperate time periods. 8am-5pm 5pm-8pm 8pm-8am I can use a where statement in my querey but it only allows for one at a time(bold below)

select * from mwappts
where company = 'nvra'
and adate between @start and @end
and book = @book
and akeytime between '08:00' and '16:59'
and (userflag = 'b' or userflag = 'u')

I would like to be able to list it like this.

Date 8am-5pm 5pm-8pm 8pm-8am
12/1/2009 12 6 10
12/2/2009 14 9 6

I have the matrix layed out just need to know how to count conditionally. The other issue is the time that I want to use if formated like this (08:00)

Any help would be welcome

Thanks

Joshua

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:22:31

[code]select dateadd(hh,-8,dateadd(dd,datediff(dd,0,akeytime ),0)),
sum(case when datepart(hh,dateadd(hh,-8,akeytime)) between 0 and 9 then 1 else 0 end) as [8am-5pm],
sum(case when datepart(hh,dateadd(hh,-8,akeytime)) between 9 and 12 then 1 else 0 end) as [5pm-8pm],
sum(case when datepart(hh,dateadd(hh,-8,akeytime)) between 12 and 23 then 1 else 0 end) as [8pm-8am]
from mwappts
where company = 'nvra'
and adate between @start and @end
and book = @book
and akeytime between '08:00' and '16:59'
and (userflag = 'b' or userflag = 'u')
group by dateadd(hh,-8,dateadd(dd,datediff(dd,0,akeytime ),0))
[/code]

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

jhaney
Starting Member

33 Posts

Posted - 2010-02-15 : 14:01:44

Thanks for the reply I just figured it out. I used the following expression

=Sum(iif(Fields!AKEYTIME.Value>="08:00",1,0)and(iif(Fields!AKEYTIME.Value<="16:59",1,0)))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 00:59:06

quote:
Originally posted by jhaney

Thanks for the reply I just figured it out. I used the following expression

=Sum(iif(Fields!AKEYTIME.Value>="08:00",1,0)and(iif(Fields!AKEYTIME.Value<="16:59",1,0)))

were you asking for reporting services expression? I thought you were asking for t-sql query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources