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
 General SQL Server Forums
 New to SQL Server Programming
 Result discrepancy
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SqlGirl87
Starting Member

26 Posts

Posted - 10/13/2012 :  08:40:00  Show Profile  Reply with Quote
Hi,

I have 2 simple scripts that I thought should do the same thing, but they are returning difference results; can anyone spot why?


--Total: ACP, gen cont totals and difference
DECLARE @area varchar (3), @rep varchar (3), @contc varchar (80), @contr varchar (20)
 
SELECT @area = '123'
SELECT @rep = 'RTL'
SELECT @contc = g_office FROM manledg (nolock) where rep = @rep AND area = @area
SELECT @contr = m_rep FROM manledg (nolock) where rep = @rep AND area = @area
 
SELECT
(SELECT ABS(ROUND(SUM(valuel),2)) FROM areatran (nolock) WHERE area = @area AND ref_id = @contr) AS 'gt_a_value',

(SELECT ABS(ROUND(SUM(value),2)) FROM officetran (nolock) WHERE area = @area AND office = @contc) AS 'gt_g_value'


SELECT ROUND(SUM(A.value),2) as 'gt_a_value', ABS(ROUND(SUM(T.value),2)) as 'gt_g_value', T.area, L.rep, L.m_rep, L.g_office FROM areatran A 
INNER JOIN officetran T ON
A.area = T.area
INNER JOIN manledg L ON
L.area = A.area AND L.area = T.area AND L.m_rep = A.ref_id AND L.g_office = T.office AND L.rep = A.rep
GROUP by T.area, L.rep, L.g_office, L.m_rep



return result


gt_a_value           gt_g_value
---------------------- ----------------------
975292.6               1014426.72

(1 row(s) affected)

gt_a_value           gt_g_value               area    rep      m_rep        g_office
---------------------- ---------------------- ------- --------- ----------- --------------------------------------------------------------------------------
4627208263.6           3028339.16            AAR     RTL       999999      A1
435955792.2            197813210.4           123     RTL       000000NN    A2
16957.5                5679598.28             123     PL2        9999888    A3
408523346.25           1341506497.09          123     LL1           3005    A4                                                                      
41151.6                40557.6                YE3     LL1       9998        A5                                                                           

(5 row(s) affected)



I was expecting the value in the second row of the second results to match the top one, but they aren't so I have done something wrong but can't see what!

Thanks in advance for the help

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
264 Posts

Posted - 10/13/2012 :  09:46:22  Show Profile  Reply with Quote
You have to add the following condition to the second query:

 WHERE L.rep = @rep
   AND L.area = @area




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 10/13/2012 :  10:12:14  Show Profile  Reply with Quote
quote:
Originally posted by malpashaa

You have to add the following condition to the second query:

 WHERE L.rep = @rep
   AND L.area = @area




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha



Sorry, I didn't explain to well. While the first query brings back the results for that one row, I wrote the second query so I don;t have to use a variable, return all entries; I thought the join statement was working it out for me, but isn't
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
264 Posts

Posted - 10/13/2012 :  10:35:28  Show Profile  Reply with Quote
quote:
Originally posted by SqlGirl87
Sorry, I didn't explain to well. While the first query brings back the results for that one row, I wrote the second query so I don;t have to use a variable, return all entries; I thought the join statement was working it out for me, but isn't


Then try something like this:

SELECT L.area, L.rep, L.m_rep, L.g_office, A.gt_a_value, T.gt_g_value
  FROM manledg AS L
       CROSS APPLY
       (SELECT ABS(ROUND(SUM(valuel),2))
          FROM areatran AS A
         WHERE A.area = L.area
           AND A.ref_id = L.m_rep) AS A(gt_a_value),
       CROSS APPLY
       (SELECT ABS(ROUND(SUM(value),2))
          FROM officetran AS T
         WHERE T.area = L.area
           AND T.office = L.g_office) AS T(gt_g_value)




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Edited by - malpashaa on 10/13/2012 10:36:15
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 10/13/2012 :  11:01:11  Show Profile  Reply with Quote
quote:
Originally posted by malpashaa

quote:
Originally posted by SqlGirl87
Sorry, I didn't explain to well. While the first query brings back the results for that one row, I wrote the second query so I don;t have to use a variable, return all entries; I thought the join statement was working it out for me, but isn't


Then try something like this:

SELECT L.area, L.rep, L.m_rep, L.g_office, A.gt_a_value, T.gt_g_value
  FROM manledg AS L
       CROSS APPLY
       (SELECT ABS(ROUND(SUM(valuel),2))
          FROM areatran AS A
         WHERE A.area = L.area
           AND A.ref_id = L.m_rep) AS A(gt_a_value),
       CROSS APPLY
       (SELECT ABS(ROUND(SUM(value),2))
          FROM officetran AS T
         WHERE T.area = L.area
           AND T.office = L.g_office) AS T(gt_g_value)




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha



Excellent, thanks - haven't come across the 'cross apply' before.
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.05 seconds. Powered By: Snitz Forums 2000