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)
 Join is adding data incorrectly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

112 Posts

Posted - 04/10/2013 :  00:37:24  Show Profile  Reply with Quote
I have a join and its adding data to my count incorrectly, let me show you.

WRONG COUNT:
DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();

select agent_login,count(TOT_CALLS_HANDLED) as ChatNumbers
from Chatonly
join Agents ag on agent_login=ag.agent_login_id
where STATISTIC_DATE
BETWEEN @startdate AND @enddate
and agent_login_id='leeadam'
and TOT_CALLS_HANDLED >= 1
group by agent_login

This returns:
leeadam 312

CORRECT COUNT:
DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();

select agent_login,count(TOT_CALLS_HANDLED) as PhoneNumbers
from Phoneonly
--left join Agents ag on agent_login=ag.agent_login_id
where STATISTIC_DATE
BETWEEN @startdate AND @enddate
and agent_login='leeadam'
and TOT_CALLS_HANDLED >= 1
group by agent_login

this returns
leeadam 7

I do need this join as it is a subquery within a bigger query, so the join is 100% required.


visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/10/2013 :  00:48:58  Show Profile  Reply with Quote
sorry i dont get your issue. the two queries are entirely different. Even tables involved are different Chatonly against Phoneonly. then how do you expect them to give the same count?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 04/10/2013 :  01:30:53  Show Profile  Reply with Quote
Sorry my bad lets try again.

wrong count being produced

DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();

select agent_login,count(TOT_CALLS_HANDLED) as PhoneNumbers
from Phoneonly
left join Agents ag on agent_login=ag.agent_login_id
where STATISTIC_DATE
BETWEEN @startdate AND @enddate
and agent_login='leeadam'
and TOT_CALLS_HANDLED >= 1
group by agent_login

produces
leeadam 56

correct count.

DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();

select agent_login,count(TOT_CALLS_HANDLED) as PhoneNumbers
from Phoneonly
--left join Agents ag on agent_login=ag.agent_login_id
where STATISTIC_DATE
BETWEEN @startdate AND @enddate
and agent_login='leeadam'
and TOT_CALLS_HANDLED >= 1
group by agent_login


this returns
leeadam 7
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/10/2013 :  01:37:12  Show Profile  Reply with Quote
thats because you've many to many relationship existing between Agents and Phoneonly tables i guess

can there be multiple agents for same call?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 04/10/2013 :  02:48:58  Show Profile  Reply with Quote
Ok So how might I be able to maybe distinct this? With the join there is nothing else that I can append to within the 'agents' table and as I said I need it.

here are the resultss without the count

leeadam 2
leeadam 2
leeadam 2
leeadam 2
leeadam 2
leeadam 2
leeadam 2
leeadam 2
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
leeadam 1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/10/2013 :  03:10:00  Show Profile  Reply with Quote
not an easy way to do it unless you've a field which identifies each unique instances of TOT_CALLS_HANDLED value

Are you telling you dont have an id or date field to identify unique records in PhoneOnly table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 04/10/2013 :  04:16:21  Show Profile  Reply with Quote
I have mapped out the data structure with some data attached so you can see, there are 2 sheets within.
http://www.sendspace.com/file/5xmrf2
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/10/2013 :  04:45:48  Show Profile  Reply with Quote
so you just want to count instances (no of records) for agent in phone table right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 04/10/2013 :  19:46:19  Show Profile  Reply with Quote
You got it! that's what I am after
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.09 seconds. Powered By: Snitz Forums 2000