I have a situation where I want to sum difference between two tables. problem is that a row can exist in the second table and then I want to insert it as new row.
Pseudo
SELECT T1.seller, T1.code, T1.amount - T2.amount
Look at the image for an explanation
DECLARE @T1 TABLE(
seller VARCHAR(10),
code VARCHAR(3) NULL,
amount MONEY
)
DECLARE @T2 TABLE(
seller VARCHAR(10),
code VARCHAR(3) NULL,
amount MONEY
)
INSERT INTO @T1 VALUES
('VL',NULL,1),
('VL','317',70005.6)
INSERT INTO @T2 VALUES
('VL',NULL,0.5),
('VL','500',4450)
SELECT seller,code,SUM(amount) [amount] FROM
(
SELECT * FROM @T1
UNION ALL
SELECT seller,code,-amount as amount FROM @T2
) T
GROUP BY seller,code
You'll need to make a full outer join - then sum it up. If you run only the inner query you will get each possible row combination (exclusive in t1, exist both in t1 and t2, exlclusive in t2) - you then group it up and do the sum.
SELECT Seller ,
Code ,
SUM(Tab1_amt - Tab2_amt) AS Amount
FROM ( SELECT COALESCE(tab1.Seller, tab2.Seller) AS Seller ,
COALESCE(tab1.code, tab2.code) AS Code ,
COALESCE(tab1.amount, 0) AS tab1_amt ,
COALESCE(tab2.amount, 0) AS tab2_amt
FROM tab1
FULL OUTER JOIN tab2 ON tab1.seller = tab2.seller
AND tab1.code = tab2.code
) AS Tbl
GROUP BY Seller ,
Code
See SQLFiddle Demo
I think you need a FULL JOIN (unless you actually want the row inserting to the first table)
SELECT COALESCE(t1.Seller, t2.Seller) AS Seller,
COALESCE(t1.Code, t2.Code) AS Code,
COALESCE(t1.Amount, 0) - COALESCE(t2.Amount, 0) AS Amount
FROM Table1 t1
FULL JOIN Table2 t2
ON t1.Seller = t2.Seller
AND COALESCE(t1.Code, 0) = COALESCE(t2.Code, 0);
If you do need the row inserting to table 1 you will need to do 2 operations, first insert, then select:
INSERT Table1 (Seller, Code, Amount)
SELECT t2.Seller, t2.Code, 0 AS Amount
FROM Table2 t2
WHERE NOT EXISTS
( SELECT 1
FROM Table1 t1
WHERE t1.Seller = t2.Seller
AND COALESCE(t1.Code, 0) = COALESCE(t2.Code, 0)
);
SELECT t1.Seller,
t1.Code,
t1.Amount - COALESCE(t2.Amount, 0) AS Amount
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.Seller = t2.Seller
AND COALESCE(t1.Code, 0) = COALESCE(t2.Code, 0);
EDIT
IF rows in each table are not unqiue and you need to sum them then you will need to do the sums in subqueries as the JOIN will introduce cross joining:
Consider this data
Table1
Seller Code Amount
VL 500 10
VL 500 20
Table2
Seller Code Amount
VL 500 30
VL 500 5
When you join this you will get:
t1.Seller t1.Code t1.Amount t2.Seller t2.Code t2.Amount
VL 500 10 VL 500 30
VL 500 10 VL 500 5
VL 500 20 VL 500 30
VL 500 20 VL 500 5
The sum of the difference is then -10 instead of -5.
SELECT COALESCE(t1.Seller, t2.Seller) AS Seller,
COALESCE(t1.Code, t2.Code) AS Code,
COALESCE(t1.Amount, 0) - COALESCE(t2.Amount, 0) AS Amount
FROM ( SELECT Seller, Code, SUM(Amount) AS Amount
FROM Table1
GROUP BY Seller, Code
) t1
FULL JOIN
( SELECT Seller, Code, SUM(Amount) AS Amount
FROM Table2
GROUP BY Seller, Code
) t2
ON t1.Seller = t2.Seller
AND COALESCE(t1.Code, 0) = COALESCE(t2.Code, 0);
EDIT 2
The UNION method in Daniel's answer will perform much better than the FULL JOIN:
SELECT Seller, Code, Amount = SUM(Amount)
FROM ( SELECT Seller, Code, Amount
FROM Table1
UNION
SELECT Seller, Code, -Amount
FROM Table2
) t
GROUP BY Seller, Code
Something like this? your not telling what table to insert into "therefore tableX"
insert into tableX (seller,code,amount) values (T1.seller, T1.code, T1.amount -T2.amount)
select count(*) from table2 having count(*) < 1
notice you put the filter as a select statement after your insert to apply filter you didn't specify what type of sql you need, so i cant say if this works
