0

I am in need of performing a calculation with a where clause based off two date fields in the joined two tables. I have set-up the query like such, however the returned results I get are

SalesPerson SalesAmt    DiffAmt
G           22          -66

And my expected result set is

SalesPerson SalesAmt    DiffAmt
 G           11         -33

Below is sample DDL and the query I attempted to get my desired ouput. What should be set-up differently in order to obtain my desired output?

Declare @Main Table (sn varchar(100), amt float, sd date)
Declare @Sub Table (sn varchar(100), samt float, ssd date)

Insert Into @Main (sn, amt, sd) Values
('G', 23.00, '20170601'), ('G', 21.00, '20170602'), ('G', 33.00, '20170608')

Insert Into @Sub (sn, samt, ssd) VALUES
('G', 11.00, '20170502'), ('G', 12.00, '20170601'), ('G', -1.00, '20170601')

Select
SalesPerson = m.sn
,SalesAmt = SUM(s.samt)
,DiffAmt = Sum(s.samt-m.amt)
FROM @Main m
Join @Sub s
On m.sn = s.sn
Where m.sd >= '20170601' And m.sd <= '20170605'
And s.ssd >= '20170601' And s.ssd <= '20170605'
GROUP BY m.sn
4

You could get aggregates before to join both tables.

;WITH g1 AS
(
    SELECT sn, SUM(samt) AS samt
    FROM   @Sub
    WHERE  ssd >= '20170601' AND ssd <= '20170605'
    GROUP BY sn
)
, g2 AS
(
    SELECT sn, SUM(amt) as amt
    FROM   @Main
    WHERE  sd >= '20170601' AND sd <= '20170605'
    GROUP BY sn
)
SELECT g1.sn AS SalesPerson,
       g1.samt AS SalesAmt,
       (g1.samt - g2.amt) AS DiffAmp
FROM   g1
JOIN   g2
ON     g1.sn = g2.sn;
GO
SalesPerson | SalesAmt | DiffAmp
:---------- | -------: | ------:
G           |       11 |     -33

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.