Thread: Query Help - Count View Single Post
April 21st, 2015, 01:56 AM
spooonmanz
Registered User
 Points: 5, Level: 1
 Activity: 0%

Join Date: Apr 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Query Help - Count

Hi,
I'm new to the forum and learning SQL query but with the help of a 2006 post, I'm close to solving a reporting request but seem to have run into an issue I can not solve.

The source table being queried has call data such as start time in date time format, end time, location id, called from number, called to number, etc... Many columns of data. The desired output is to have a cross tab with office id, month, day as rows and column headers of morning, afternoon and evening. Where the time is based on the start time of the call. Morning is 00 - 12noon, afternoon is 12noon-5p and evening is 5pm - midnight.

I built the following query based on a response in this forum from 2006 to retrieve specific months and year. Many many thanks for the insight. After reading through the post and seeing how the query runs against a subset of the data, this was something I understood to a degree and could share with a co-worker who is learning with me (or vice versa).
Unfortunately, there are two issues with my query after checking my work...
1) A call made at 1:00p would show up under morning and afternoon.
2) If a day has 3 calls in the 11am hour, only one is counted instead of 3 because they are all at 11a. If one call is made at each hour 8a,9a and 10a, then the query counts all 3 calls. It seems the results are a count of the number of unique hour versus total count of calls for the day and timeframe.

select d.Called_Office_id,d.[Month],d.[DAY],d.[YEAR],
sum(case when ([Hour] between 00 and 12) THEN 1 else 0 END) as [Morning],
sum(case when ([Hour] between 12 and 17) then 1 else 0 END) as [Afternoon],
sum(case when ([Hour] between 17 and 24) then 1 else 0 END) as [Evening]
from
(
select 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(hh,start),
DATEPART(dd,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]

Thank you
Sp