p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2008 (http://p2p.wrox.com/forumdisplay.php?f=484)
-   -   Query Help - Count (http://p2p.wrox.com/showthread.php?t=94415)

spooonmanz April 21st, 2015 01:56 AM

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

mmorgan30 April 26th, 2015 01:05 AM

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]


All times are GMT -4. The time now is 05:37 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.