SQLTeam.com | Weblogs | Forums

How to get balance from three table like issue, receive and other

I have three tables 1] Receive 2]Issue and 3]Melting. I have to calculate balance from these three table like. Voucher No and wt. are common fields. I want balance = receive - issue + melting

My approach is that union two tables (Issue and Melting) - Receive table but it showing wrong output. I need below output which has been posted

Receipt
Voucher No Wt
VC005 500
Issue
Voucer No Wt
VC005 200
VC005 100
Melting
Voucer No wt
VC005 100
OutPut A - (B+C)
Voucer No Receipt - Issue + Melting
VC005 100

Thanks & Regards

Nandan Navale


SELECT [Voucher No], SUM(Receipt) - (MAX(Issue) + MAX(Melting)) AS Balance
FROM Receipt R
INNER JOIN (
    SELECT [Voucher No], SUM(Issue) AS Issue
    FROM Issue
    GROUP BY [Voucher No]
) AS I ON I.[Voucher No] = R.[Voucher No]
INNER JOIN (
    SELECT [Voucher No], SUM(Melting) AS Melting
    FROM Melting
    GROUP BY [Voucher No]
) AS M ON M.[Voucher No] = R.[Voucher No]
GROUP BY [Voucher No]

Thank you very much Scott. its work perfectly.