# Performing Calculations WIth Join

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

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