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 ChatNumbersfrom Chatonlyjoin Agents ag on agent_login=ag.agent_login_idwhere STATISTIC_DATEBETWEEN @startdate AND @enddate and agent_login_id='leeadam'and TOT_CALLS_HANDLED >= 1group by agent_loginThis returns:leeadam 312CORRECT COUNT:DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';DECLARE @enddate datetime2 = GETDATE(); select agent_login,count(TOT_CALLS_HANDLED) as PhoneNumbersfrom Phoneonly--left join Agents ag on agent_login=ag.agent_login_idwhere STATISTIC_DATEBETWEEN @startdate AND @enddateand agent_login='leeadam'and TOT_CALLS_HANDLED >= 1group by agent_loginthis returnsleeadam 7I 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-04-10 : 01:30:53
|
Sorry my bad lets try again.wrong count being producedDECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';DECLARE @enddate datetime2 = GETDATE(); select agent_login,count(TOT_CALLS_HANDLED) as PhoneNumbersfrom Phoneonlyleft join Agents ag on agent_login=ag.agent_login_idwhere STATISTIC_DATEBETWEEN @startdate AND @enddateand agent_login='leeadam'and TOT_CALLS_HANDLED >= 1group by agent_loginproducesleeadam 56correct count.DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';DECLARE @enddate datetime2 = GETDATE(); select agent_login,count(TOT_CALLS_HANDLED) as PhoneNumbersfrom Phoneonly--left join Agents ag on agent_login=ag.agent_login_idwhere STATISTIC_DATEBETWEEN @startdate AND @enddateand agent_login='leeadam'and TOT_CALLS_HANDLED >= 1group by agent_loginthis returnsleeadam 7 |
|
|
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 guesscan there be multiple agents for same call?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 countleeadam 2leeadam 2leeadam 2leeadam 2leeadam 2leeadam 2leeadam 2leeadam 2leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1leeadam 1 |
|
|
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 valueAre you telling you dont have an id or date field to identify unique records in PhoneOnly table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2013-04-10 : 19:46:19
|
You got it! that's what I am after |
|
|
|