CODEWITHSUNDEEP
sql-server
Ibanez1408
Ibanez1408

Reputation: 5078

Sum values from 2 tables and subtract it to each other

I have 2 tables, ord_tbl and pay_tbl. o_tbl with these data.

ord_tbl

invoice | emp_id | prod_id | amount
123     | 101    | 1       | 1000
123     | 101    | 2       | 500
123     | 101    | 3       | 500
124     | 101    | 2       | 300
125     | 102    | 3       | 200

pay_tbl

invoice | new_invoice | amount
123     | 321         | 300
123     | 322         | 200
124     | 323         | 300
125     | 324         | 100

I would like the selection statement to give me this result

invoice | emp_id | orig_amt | balance | status
123     | 101    | 2000     | 1500    | unsettled

The invoice that has 0 balance will not be included anymore. I know that I have to use the join and sub queries here but I don't even know how to start it! For me, as a beginner, this is very complex already. This is what I tried so far...

SELECT 
  ord_tbl.invoice,
  SUM(ord_tbl.amount) As 'origAmt',
  SUM(pay_tbl.amount) As 'payAmt', 
  origAmt - payAmt As 'bal' 
FROM 
  ord_tbl 
  INNER JOIN pay_tbl 
    ON ord_tbl.invoice = pay_tbl.invoice 
WHERE 
  ord_tbl.emp_id = @emp_id AND 
  bal != 0 
GROUP BY 
  ord_tbl.invoice

Upvotes: 0

Views: 49

Answers (1)

Mihai
Mihai

Reputation: 26784

You need to prepare your data joining on a non unique field will lead to a cross JOIN,that`s why you get 4000

;WITH CTE as
(SELECT ot.invoice,MAX(ot.emp_id) as emp_id,SUM(ot.amount) as origAmt FROM ord_tbl ot GROUP BY ot.invoice),
CTE2 as
( SELECT pt.invoice,SUM(pt.ammount) as payAmt FROM pay_tbl pt GROUP BY pt.invoice)
SELECT CTE.invoice,CTE.emp_id,CTE.origAmt,CTE.origAmt-NULLIF(CTE2.payAmt,0) as bal,'unsettled' as status
FROM
CTE LEFT JOIN CTE2 ON CTE.invoice=CTE2.invoice 
AND CTE.emp_id=101 AND CTE.origAmt-NULLIF(CTE2.payAmt,0)>0

Upvotes: 1

Related Questions