| 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 |
|
Some odd results - incorrect numbers