--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

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)

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

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

---

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

---

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)

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

---