Some odd results - incorrect numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Some odd results - incorrect numbers

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-18 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 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

550 Posts

Posted - 2013-03-18 : 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 - 2013-03-18 : 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

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-18 : 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!!!!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-18 : 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 - 2013-03-19 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 01:26:29

use either po.STATISTIC_DATE or co.STATISTIC_DATE

--
Chandu

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-19 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 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

550 Posts

Posted - 2013-03-19 : 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 - 2013-03-19 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-19 : 17:52:15

What is the datatype of statistics_date?

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-19 : 18:03:48

simple datetime.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-19 : 18:08:27

So this - statistics_date >= '20130101' - Genrates a datatype conversion error?

EDIT: Maybe you should post the query you are using. Also, here are some links to help you post sample data and expected ouput. It makes it much easier to help you!

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-19 : 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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-19 : 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 - 2013-03-19 : 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!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources