June 19, 2009 at 5:57 am
Good day all,
It is newbie Byron here with another issue that I am sure you are all going to find very easy 🙂 So here it goes
I have 3 tables with the same column names warehousing stats for 3 different skill sets (this is a call centre question). What I want to do is sum up the number of calls offered per day, per interval for all 3 skillsets and to display as one result
I have read somewhere that union is what I must use but I am unable to get the query to do what I want. I would post what I have done but it is probably way wrong. Please could someone guide me in terms of what the query must look like, I will obviously replace the table names and columns with the correct names. The columns that I am using in my tables are
Date
Interval
Calls Offered
ACD Calls
Thanking you all in advance
Byron
Aban Calls
June 19, 2009 at 6:17 am
It would be better if you post the structure of your tables and some sample data - the result set that you want to see... and I'm sure that somebody will do the best for you!
June 19, 2009 at 6:35 am
Query resolved. Will be a while before I post again as I obviously need to learn more before I ask for assistance. Dont even understand what I need to do with the code in the best practises tutorial
select date,
Interval,
sum(callsOffered) as "CallsOfferedTotal"
from (select date, interval, callsoffered
from dbo.??_13
UNION
select date, interval, callsoffered
from dbo.??_70
UNION
select date, interval, callsoffered
from dbo.??_71) tbl
group by date, interval
order by date, interval
June 19, 2009 at 6:45 am
Ok for you information about UNION!
UNION - select distinct values or better to say removes duplicate values
UNION ALL - select all values available and much faster than UNION!
So, if you know that all records in your result set are unique with UNION, use UNION ALL instead (better performance and faster than UNION)!
Have a nice SQLing in the SSC forum!
😉
June 19, 2009 at 6:49 am
Hi Byron,
It's a bit difficult to get the requirements you are after exactly right without seeing some test data / structures etc.
From what you have said though, it looks like you can get it done without having to use any unions. So just for fun:
--Test Data
--
select * into #Table1 from (
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 1 as Interval, 23 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 2 as Interval, 30 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 4 as Interval, 32 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 3 as Interval, 18 as CallsOffered) as _int
--
select * into #Table2 from (
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 1 as Interval, 3 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 2 as Interval, 6 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 4 as Interval, 2 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 3 as Interval, 1 as CallsOffered) as _int
--
select * into #Table3 from (
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 1 as Interval, 54 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 2 as Interval, 82 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 5 as Interval, 90 as CallsOffered
union all
select cast(datediff(day,0,GETDATE()) as smalldatetime) as CallDate, 3 as Interval, 12 as CallsOffered) as _int
--
-- Query
--
select isnull(t1.CallDate,isnull(t2.CallDate,t3.CallDate)) as CallDate,
isnull(t1.Interval,ISNULL(t2.interval,t3.interval)) as Interval,
SUM(coalesce(t1.callsoffered,0)+coalesce(t2.callsoffered,0)+coalesce(t3.callsoffered,0)) as CallsOfferedTotal
from #Table1 t1 full join
#Table2 t2 on
t1.CallDate = t2.CallDate and
t1.Interval = t2.Interval full join
#Table3 t3 on
t2.CallDate = t3.CallDate and
t2.Interval = t3.Interval
group by isnull(t1.CallDate,isnull(t2.CallDate,t3.CallDate)),
isnull(t1.Interval,ISNULL(t2.interval,t3.interval))
--
-- Clear up
--
drop table #Table1
drop table #Table2
drop table #Table3
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
June 19, 2009 at 6:50 am
Thanks for that man 🙂
Much appreciated and thanks for the quick responses.
Cheers,
Byron
June 19, 2009 at 6:53 am
byron.vanwyk (6/19/2009)
Query resolved.
Well I enjoyed myself, that's the main thing! 😀
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
June 19, 2009 at 6:54 am
silly question but do you want to remove duplicate counts that occur in the same time period? Union and Union All may have different answers depending on your data
create table [dbo].[a] (mycount int, myperiod int)
go
insert into a values (10,1)
go
insert into a values (15,1)
go
select * from a
go
select * into b from a
go
select * from b
go
select sum(mycount), myperiod from a group by myperiod
union
select sum(mycount), myperiod from b group by myperiod
go
select sum(mycount), myperiod from a group by myperiod
union all
select sum(mycount), myperiod from b group by myperiod
June 19, 2009 at 7:00 am
Nice code here Rob, you used some functions and joins and seems very nice alternative and somebody sure that will learn something else for the same thing!
June 19, 2009 at 7:12 am
Thanks Dugi,
Although I was a little premature with the posting (I've heard it happens to everyone and it's not a big deal), the query should have been:
--
-- Query
--
select coalesce(t1.CallDate,t2.CallDate,t3.CallDate) as CallDate,
coalesce(t1.Interval,t2.interval,t3.interval) as Interval,
SUM(coalesce(t1.callsoffered,0)+coalesce(t2.callsoffered,0)+coalesce(t3.callsoffered,0)) as CallsOfferedTotal
from #Table1 t1 full join
#Table2 t2 on
t1.CallDate = t2.CallDate and
t1.Interval = t2.Interval full join
#Table3 t3 on
t2.CallDate = t3.CallDate and
t2.Interval = t3.Interval
group by coalesce(t1.CallDate,t2.CallDate,t3.CallDate),
coalesce(t1.Interval,t2.interval,t3.interval)
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply