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)
 Join is adding data incorrectly

Author  Topic 

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-10 : 00:37:24
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

52326 Posts

Posted - 2013-04-10 : 00:48:58
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 - 2013-04-10 : 01:30:53
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

52326 Posts

Posted - 2013-04-10 : 01:37:12
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 - 2013-04-10 : 02:48:58
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

52326 Posts

Posted - 2013-04-10 : 03:10:00
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 - 2013-04-10 : 04:16:21
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

52326 Posts

Posted - 2013-04-10 : 04:45:48
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 - 2013-04-10 : 19:46:19
You got it! that's what I am after
Go to Top of Page
   

- Advertisement -