Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > SQL Server 2008
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 21st, 2015, 01:56 AM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #2 (permalink)  
Old April 26th, 2015, 01:05 AM
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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
new display count query carswelljr SQL Language 9 May 7th, 2007 04:59 PM
query count? Raif Access 1 June 18th, 2004 05:08 PM
count clause in query mateenmohd SQL Server 2000 2 June 10th, 2004 01:47 AM
SQL query for count programmer_kay ADO.NET 1 April 18th, 2004 02:48 AM
SQL query for count programmer_kay SQL Language 1 April 17th, 2004 07:30 PM



All times are GMT -4. The time now is 04:11 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.