View Single Post
  #2 (permalink)  
Old April 26th, 2015, 01:05 AM
mmorgan30 mmorgan30 is offline
Friend of Wrox
Points: 528, Level: 8
Points: 528, Level: 8 Points: 528, Level: 8 Points: 528, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2014
Posts: 136
Thanks: 1
Thanked 10 Times in 10 Posts
Default

make sure your inner select is selecting the row you think it is, it does do a group by with out a count so you may be wrapping a single hours worth of calls into a single record.

the "between" operator in inclusive of its bounds so when you say

between 00 and 12 --> it includes 00 - through and including-12

then when you say

between 12 and 17 it will include 12 -through and including- 17

so you have a record for the first between operator and a record for the 2nd between operator

think you are loking for more something like this but could be wrong

select d.Called_Office_id,d.[Month],d.[DAY],d.[YEAR],
sum(case when ([Hour] between 00 and 12) THEN d.[count] else 0 END) as [Morning],
sum(case when ([Hour] between 13 and 17) then d.[count] else 0 END) as [Afternoon],
sum(case when ([Hour] between 18 and 24) then d.[count] else 0 END) as [Evening]
from
(
select count(*) as [count],
[called_office_id],
[YEAR] = DATEPART(YYYY,[start]),
[MONTH] = DATEPART(MM,[start]),
[DAY] = DATEPART(dd,[start]),
[HOUR] = DATEPART(HH,[start])
from raw_call_data_file
group by [Called_Office_id],
DATEPART(YYYY,[start]),
DATEPART(MM,[start]),
DATEPART(dd,[start]),
DATEPART(HH,[start])

)d
where d.[YEAR] = '2014' and d.[MONTH] in (04,05,06,07,08,09,10)
group by d.Called_Office_id,d.[YEAR],d.[MONTH],d.[DAY]
with rollup
order by d.Called_Office_id,d.[MONTH],d.[DAY]

Last edited by mmorgan30; April 26th, 2015 at 01:53 AM..