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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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  
 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.06 seconds. Powered By: Snitz Forums 2000