May 19, 2008 at 2:45 pm
I cant seem to wrap my head around this one .....
I have a Table in a DB that tracks all phone calls made from this office on a particular PRI. The DB contains fields like unique_id, numbers and termcodes ( answered call, hungup on, no answer, etc ). Id like to be able to write a query to find out .....
Based on a unique_ID and all calls made between say a week ago and now ... Unique_id has X OI's and Y HU's and Z NA's.
the fields that matter are ...
Unique_ID = PK
termcd= termcode
calldate = call date
What the result might look like is .....
uniqueid OI NA HU BM CF BS
123456 55 44 56 0 1 89
987654 9 55 4 99 78 2
Thanks for any help!
May 19, 2008 at 3:10 pm
The query to pull back the data with the aggregate values is quite simple.
SELECT Unique_ID, tempcd, COUNT(*)
FROM TableA
WHERE calldate > DATEADD(dd,-7,GETDATE())
GROUP BY Unique_ID, tempcd
It looks, though, like you want to display the result set horizontally, which means you'll need to pivot the data. You've posted this in a SQL Server 2000 thread, so I'll assume that the 2005 PIVOT command won't help you much.
There are many, many threads here on SSC that can assist you with pivoting the data if you need to display it that way. Search SSC and post questions if you have more.
May 19, 2008 at 3:16 pm
Use this and what John posted to get you going:
create table dbo.TableB (
UniqueID int,
termcd char(2),
calldate datetime
)
insert into dbo.TableB
select 1, 'OI', getdate() - 10 union all
select 1, 'NA', getdate() - 9 union all
select 1, 'NA', getdate() - 8 union all
select 1, 'HU', getdate() - 7 union all
select 1, 'HU', getdate() - 6 union all
select 1, 'BM', getdate() - 5 union all
select 1, 'CF', getdate() - 4 union all
select 1, 'BS', getdate() - 3 union all
select 1, 'NA', getdate() - 2 union all
select 1, 'NA', getdate() - 1 union all
select 1, 'NA', getdate() - 0
select * from dbo.TableB
select
UniqueID,
sum(case termcd when 'OI' then 1 else 0 end) as OI,
sum(case termcd when 'NA' then 1 else 0 end) as NA,
sum(case termcd when 'HU' then 1 else 0 end) as HU,
sum(case termcd when 'BM' then 1 else 0 end) as BM,
sum(case termcd when 'CF' then 1 else 0 end) as CF,
sum(case termcd when 'BS' then 1 else 0 end) as BS
from
dbo.TableB
group by
UniqueID
drop table dbo.TableB
😎
May 19, 2008 at 3:22 pm
I guess Im a total newbe cause I have no idea how to integrate Johns with yours.
May 19, 2008 at 3:54 pm
After playing around a little ... I was able to get this to work ....
select
UniqueID,
sum(case termcd when 'BM' then 1 else 0 end) as BM,
sum(case termcd when 'HU' then 1 else 0 end) as HU,
sum(case termcd when 'LM' then 1 else 0 end) as LM,
sum(case termcd when 'NA' then 1 else 0 end) as NA,
sum(case termcd when 'OI' then 1 else 0 end) as OI,
sum(case termcd when 'BS' then 1 else 0 end) as BS,
sum(case termcd when 'CF' then 1 else 0 end) as CF,
sum(case termcd when 'FX' then 1 else 0 end) as FX,
sum(case termcd when 'NR' then 1 else 0 end) as NR
from calltrak
--dbo.TableB
where campaign = 'TCS' and DATEDIFF("dd",calldate,GETDATE()) <= 10
group by
UniqueID
If anyone can see any blatant errors Id appreciate feed back .... what little I tested it .. it seems to work. Im off to really test it out. Thanks John and Lynn for your help.
May 19, 2008 at 4:15 pm
I'd do the following instead (notice change in the where clause):
select
UniqueID,
sum(case termcd when 'BM' then 1 else 0 end) as BM,
sum(case termcd when 'HU' then 1 else 0 end) as HU,
sum(case termcd when 'LM' then 1 else 0 end) as LM,
sum(case termcd when 'NA' then 1 else 0 end) as NA,
sum(case termcd when 'OI' then 1 else 0 end) as OI,
sum(case termcd when 'BS' then 1 else 0 end) as BS,
sum(case termcd when 'CF' then 1 else 0 end) as CF,
sum(case termcd when 'FX' then 1 else 0 end) as FX,
sum(case termcd when 'NR' then 1 else 0 end) as NR
from
dbo.calltrak
--dbo.TableB
where
campaign = 'TCS'
and calldate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 10
and calldate < dateadd(dd, datediff(dd, 0, getdate()), 0) + 1
group by
UniqueID
😎
May 19, 2008 at 4:20 pm
Awesome ... done deal ... Thanks sooooo much !!!!!;)
May 19, 2008 at 4:25 pm
Good solution Lynn. For some reason, I was thinking that the termcd values were not all known, hence the need for a pivot as I was actually thinking of a dynamic pivot with unknown values.
Again, good job.
May 20, 2008 at 8:00 am
riverswillbeer (5/19/2008)
Awesome ... done deal ... Thanks sooooo much !!!!!;)
Your welcome. Just to follow-up though, do you know why I made the suggested change in your WHERE clause?
😎
May 20, 2008 at 8:04 am
John Rowan (5/19/2008)
Good solution Lynn. For some reason, I was thinking that the termcd values were not all known, hence the need for a pivot as I was actually thinking of a dynamic pivot with unknown values.Again, good job.
Thank you. I just based it on the OP's post.
😎
May 20, 2008 at 9:00 am
May 20, 2008 at 9:50 am
John Rowan (5/20/2008)
I just based it on the OP's post.
What a novel concept.....:)
lol... Of course, how many times has that turned out to be NOT what they want. There is always those that leave out critical information until AFTER you have done the work.
😎
May 20, 2008 at 9:56 am
Lynn Pettis (5/20/2008)
John Rowan (5/20/2008)
I just based it on the OP's post.
What a novel concept.....:)
lol... Of course, how many times has that turned out to be NOT what they want. There is always those that leave out critical information until AFTER you have done the work.
😎
I try never to listen to those pesky end-users either. They tend to get in the way of a perfectly clean ER diagram....:P
"Exceptions??? you're not ALLOWED any exceptions...."
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 11:07 am
Lynn Pettis (5/20/2008)
riverswillbeer (5/19/2008)
Awesome ... done deal ... Thanks sooooo much !!!!!;)Your welcome. Just to follow-up though, do you know why I made the suggested change in your WHERE clause?
😎
Well, to be perfectly frank, I have the basics down on T-SQL, and thats it. I know from reading forums and tutorials that there are many ways to code sql and depending on your mentor or schooling people have certain ways of coding. Some are accepted and some are not. I figured that my "just keep googling till it works, however it works" 'where statement' was either flawed or not accepted as the correct method. Now looking at your 'where statement' closer ... I thought maybe it was for making sure today was added into the mix... however I tested for it previously and the original where worked. So I guess I assume its an accepted method reason.
On that note .... I really enjoy trying to solve these SQL puzzles for work ... I think its cool that I can solve puzzles and get paid for it. Every now and again .. I need to ask for some guidance and these forums are a godsend. Thank you for taking your time to help an upcoming SQL junkie. This is the first time anyone has asked me ... "Why did I do it that way" and on a learning level .. that to me is invaluable. Its taking my learning one step farther. I feel sometimes that Im already asking alot to have a pro help me with some code .. so the last thing I want to do is bug them for an explanation. Thanks for your guidance and godbless.
May 20, 2008 at 12:04 pm
Actually, it came down to this, your WHERE clause would not make use of an index on the date field if available, where as the change I made would allow the update query to make use of an index on the date field.
My change was for performance/scalability.
I hope that helps you as you continue to learn more about SQL and MS SQL Server to be specific.
😎
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply