SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Some odd results - incorrect numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

102 Posts

Posted - 03/18/2013 :  02:00:39  Show Profile  Reply with Quote
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
2202 Posts

Posted - 03/18/2013 :  02:24:13  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
340 Posts

Posted - 03/18/2013 :  16:43:34  Show Profile  Reply with Quote
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
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/18/2013 :  17:09:37  Show Profile  Reply with Quote
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
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/18/2013 :  17:14:00  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
340 Posts

Posted - 03/18/2013 :  18:03:02  Show Profile  Reply with Quote
DOH, sorry, I divided twice by 3600, meant to take that out of the inner query.
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/19/2013 :  01:23:20  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/19/2013 :  01:26:29  Show Profile  Reply with Quote
use either po.STATISTIC_DATE or co.STATISTIC_DATE



--
Chandu
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/19/2013 :  02:23:38  Show Profile  Reply with Quote
I tried that before sorry i should have noted that:

Msg 207, Level 16, State 1, Line 22
Invalid column name 'STATISTIC_DATE'.


Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/19/2013 :  02:42:08  Show Profile  Reply with Quote

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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
340 Posts

Posted - 03/19/2013 :  11:01:47  Show Profile  Reply with Quote
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

Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/19/2013 :  17:36:29  Show Profile  Reply with Quote
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.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 03/19/2013 :  17:52:15  Show Profile  Reply with Quote
What is the datatype of statistics_date?
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/19/2013 :  18:03:48  Show Profile  Reply with Quote
simple datetime.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 03/19/2013 :  18:08:27  Show Profile  Reply with Quote
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

Edited by - Lamprey on 03/19/2013 18:13:32
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/19/2013 :  18:18:42  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 03/19/2013 :  18:20:50  Show Profile  Reply with Quote
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'
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 03/19/2013 :  18:26:35  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/20/2013 :  00:58:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000