Author |
Topic  |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/18/2013 : 02:00:39
|
Its best If i share results and queries to explain this one.
Select agent_login,sum(tot_talk_time) from Phoneandchat where agent_login = 'xxxxxx' group by agent_login
RESULTS xxxxxx 103290
Select agent_login,sum(tot_talk_time) from Chatonly where agent_login = 'xxxxxx' group by agent_login
Results xxxxxx 91722
Select agent_login,sum(tot_talk_time) from phoneonly where agent_login = 'xxxxxx' group by agent_login
Results xxxxxx 11568
These add to to be the top number, so these results are correct, now when I join these tables I get some very inflated numbers
Select co.agent_login, sum(co.tot_talk_time) as ChatTime, sum(co.tot_talk_time)/3600 as ChatHours, sum(po.tot_talk_time) as PhoneTime, sum(po.tot_talk_time)/3600 as PhoneHours from chatonly co join phoneonly po on co.agent_login=po.agent_login where co.agent_login = 'xxxxxx' group by co.agent_login
Results xxxxxx 19078176 5299.493333 4071936 1131.093333
Can someone help me with the query, I am not sure whats going on here? |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/18/2013 : 02:24:13
|
try this.......... Select DISTINCT co.agent_login, sum(co.tot_talk_time) OVER(PARTITION BY co.agent_login) as ChatTime, sum(co.tot_talk_time) OVER(PARTITION BY co.agent_login)/3600 as ChatHours, sum(po.tot_talk_time) OVER(PARTITION BY po.agent_login) as PhoneTime, sum(po.tot_talk_time) OVER(PARTITION BY po.agent_login) /3600 as PhoneHours from chatonly co join phoneonly po on co.agent_login=po.agent_login where co.agent_login = 'xxxxxx'
-- Chandu |
 |
|
ScottPletcher
Aged Yak Warrior
USA
550 Posts |
Posted - 03/18/2013 : 16:43:34
|
Select coalesce(co.agent_login, po.agent_login) as agent_login, co.ChatTime, co.ChatTime / 3600 AS ChatHours, po.ChatTime, po.ChatTime / 3600 AS PhoneHours from ( select agent_login,sum(tot_talk_time)/3600 as ChatTime from Chatonly where agent_login = 'xxxxxx' group by agent_login ) as co full outer join ( select agent_login,sum(tot_talk_time)/3600 as PhoneTime from Phoneonly where agent_login = 'xxxxxx' group by agent_login ) as po on co.agent_login = po.agent_login
|
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/18/2013 : 17:09:37
|
Hi Guys, Appreciate the replies but both render incorrect results.
Select coalesce(co.agent_login, po.agent_login) as agent_login, co.ChatTime, co.ChatTime / 3600 AS ChatHours, po.PhoneTime, po.PhoneTime / 3600 AS PhoneHours from ( select agent_login,sum(tot_talk_time)/3600 as ChatTime from Chatonly where agent_login = 'xxxxxx' group by agent_login ) as co full outer join ( select agent_login,sum(tot_talk_time)/3600 as PhoneTime from Phoneonly where agent_login = 'xxxxx' group by agent_login ) as po on co.agent_login = po.agent_login
Results
xxxx 19078176 5299.493333 4071936 1131.093333
Other one: Select DISTINCT co.agent_login, sum(co.tot_talk_time) OVER(PARTITION BY co.agent_login) as ChatTime, sum(co.tot_talk_time) OVER(PARTITION BY co.agent_login)/3600 as ChatHours, sum(po.tot_talk_time) OVER(PARTITION BY po.agent_login) as PhoneTime, sum(po.tot_talk_time) OVER(PARTITION BY po.agent_login) /3600 as PhoneHours from chatonly co join phoneonly po on co.agent_login=po.agent_login where co.agent_login = 'xxxxxx'
results xxxxxx 19078176 5299.493333 4071936 1131.093333
Any other ideas?
Chat time should = 91722 phone time should = 11568
|
Edited by - 2revup on 03/18/2013 17:12:21 |
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/18/2013 : 17:14:00
|
Guys I got it well... I cant take any credit for this Scott you nailed this a few minor changes! Select coalesce(co.agent_login, po.agent_login) as agent_login, co.ChatTime, co.ChatTime / 3600 AS ChatHours, po.PhoneTime, po.PhoneTime / 3600 AS PhoneHours from ( select agent_login,sum(tot_talk_time) as ChatTime from Chatonly where agent_login = 'xxxxxxx' group by agent_login ) as co full outer join ( select agent_login,sum(tot_talk_time) as PhoneTime from Phoneonly where agent_login = 'xxxxxx' group by agent_login ) as po on co.agent_login = po.agent_login
Thanks guys your input is always appreciated!!!! |
Edited by - 2revup on 03/18/2013 17:14:35 |
 |
|
ScottPletcher
Aged Yak Warrior
USA
550 Posts |
Posted - 03/18/2013 : 18:03:02
|
DOH, sorry, I divided twice by 3600, meant to take that out of the inner query. |
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/19/2013 : 01:23:20
|
Ok so the goal posts have changed again! I need to introduce a date range on this
coalesce(co.agent_login, po.agent_login) as agent_login, co.ChatTime, co.ChatTime / 3600 AS ChatHours, po.PhoneTime, po.PhoneTime / 3600 AS PhoneHours from ( select agent_login,sum(tot_talk_time) as ChatTime from Chatonly where agent_login = 'xxxxxxx' group by agent_login ) as co full outer join ( select agent_login,sum(tot_talk_time) as PhoneTime from Phoneonly where agent_login = 'xxxxxx' group by agent_login ) as po on co.agent_login = po.agent_login where phoneandchat.STATISTIC_DATE BETWEEN '2013-01-01 00:00:00.0000000' AND GETDATE();
renders as The multi-part identifier "phoneandchat.STATISTIC_DATE" could not be bound.
Kinda knew that would happen, but this is over my head anyone able to help here? If it helps both Chatonly and Phoneonly and phoneandchat tables have the column called STATISTIC_DATE this is what I am trying to use. |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/19/2013 : 01:26:29
|
use either po.STATISTIC_DATE or co.STATISTIC_DATE
-- Chandu |
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/19/2013 : 02:23:38
|
I tried that before sorry i should have noted that:
Msg 207, Level 16, State 1, Line 22 Invalid column name 'STATISTIC_DATE'.
|
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/19/2013 : 02:42:08
|
Is there agent_login column in phoneandchat? If common column is existed in 3 tables, then coalesce(co.agent_login, po.agent_login) as agent_login, co.ChatTime, co.ChatTime / 3600 AS ChatHours, po.PhoneTime, po.PhoneTime / 3600 AS PhoneHours, pc.statistic_date from phoneandchat pc JOIN ( ..........) co on co.agent_login = pc.agent_login full outer join ( ..........) po on ......... and po.agent_login = pc.agent_login .......... WHERE pc.statistic_date between <your condition>
-- Chandu |
 |
|
ScottPletcher
Aged Yak Warrior
USA
550 Posts |
Posted - 03/19/2013 : 11:01:47
|
Select coalesce(co.agent_login, po.agent_login) as agent_login, co.ChatTime, co.ChatTime / 3600 AS ChatHours, po.PhoneTime, po.PhoneTime / 3600 AS PhoneHours from ( select agent_login,sum(tot_talk_time) as ChatTime from Chatonly where agent_login = 'xxxxxxx' and statistics_date >= '20130101' group by agent_login ) as co full outer join ( select agent_login,sum(tot_talk_time) as PhoneTime from Phoneonly where agent_login = 'xxxxxx' and statistics_date >= '20130101' group by agent_login ) as po on co.agent_login = po.agent_login
|
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/19/2013 : 17:36:29
|
thanks guys both queries result in
Msg 241, Level 16, State 1, Line 4 Conversion failed when converting date and/or time from character string. On Chandu's suggestion I have added
CAST(LEFT(pc.statistic_date ,23) AS DATETIME) and a few variances of this and no joy.
|
 |
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 03/19/2013 : 17:52:15
|
What is the datatype of statistics_date? |
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/19/2013 : 18:03:48
|
simple datetime.
|
 |
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/19/2013 : 18:18:42
|
I am not defining the date time explicitly, its a variable I am using within a report, the variables are seen as @startdate and @enddate see below for full query
use aws_cases DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000'; DECLARE @enddate datetime2 = GETDATE(); Select coalesce(co.agent_login, po.agent_login) as agent_login, coalesce(co.location, po.location) as location, co.ChatNumbers, po.PhoneNumbers, CAST(LEFT(pc.statistic_date ,23) AS DATETIME) from phoneandchat pc JOIN( select agent_login,location,sum(TOT_CALLS_HANDLED) as ChatNumbers from Chatonly join Agents ag on agent_login=ag.agent_login_id group by agent_login,location ) as co on co.agent_login = pc.agent_login full outer join ( select agent_login,location,sum(TOT_CALLS_HANDLED) as PhoneNumbers from Phoneonly join Agents ag on agent_login=ag.agent_login_id group by agent_login, location ) as po on co.agent_login = po.agent_login where pc.STATISTIC_DATE BETWEEN '@startdate' AND '@enddate'
side note the start date and end date are not declared in the report I am using, they are parameters in reporting services, I have added the declare at the top to give you a clear picture.
|
Edited by - 2revup on 03/19/2013 18:20:13 |
 |
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 03/19/2013 : 18:20:50
|
when you wrap someting in a single quotes it becomes a string literal. TGry removing the single quotes from around your variables.
DECLARE @Foo INT = 1;
SELECT @Foo, '@Foo'
|
 |
|
2revup
Posting Yak Master
112 Posts |
Posted - 03/19/2013 : 18:26:35
|
Doh.... thank you.
By the way I just wanted to comment to say thank you for everyone's help. I have never been on a forum that has been so receptive and quick to respond with + helpful comments. Thank you all so very much!
|
Edited by - 2revup on 03/19/2013 18:27:52 |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 03/20/2013 : 00:58:17
|
quote: Originally posted by 2revup
Doh.... thank you.
By the way I just wanted to comment to say thank you for everyone's help. I have never been on a forum that has been so receptive and quick to respond with + helpful comments. Thank you all so very much!
Welcome
-- Chandu |
 |
|
|
Topic  |
|