I have the query below:
- SELECT sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due
- from dbo.CashierSalesDetails c
-
- select sum(a.AmountPaid) as payment
- from dbo.tblAgentPaymentMonitoring a
My requirement is that, I should be able to deduct the "payment" from the "Due". below is the actual data of both tables
I tried this code but the answer is wrong:
- SELECT a.TaxInvoiceNumber, sum(a.AmountPaid) as payment, sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due,
-
- sum(a.AmountPaid) - sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as BAlance
-
- FROM dbo.tblAgentPaymentMonitoring a INNER JOIN
- dbo.CashierSalesDetails c ON a.TaxInvoiceNumber = c.TaxInvoiceNumber
- GROUP BY a.TaxInvoiceNumber
