SQL Server Calculations – Performing Calculations With Join

sql serversql-server-2008-r2t-sql

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

Best Answer

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

Related Question