Select 1 table, sum balance from another and join
Posted by:
John Noble
Date: September 27, 2017 07:03AM
I have a salesLedger table
customerID, invNum, payable, balance, datePaid, invoiceDate
AAA101, 100001, 10,000, 8000, 0000/00/00 2017-01-01
I have a payments table
CustomerID receiptDate, receiptAmount, allocationAmount, invNum
AAA101, 2017-01-10, 500, 500, 100001
AAA101, 2017-01-11, 1000, 1000, 100001
AAA101, 2017-01-17, 500, 500, 100001
Im trying to create a query so that I can calculate the balance eg on 2017-01-16
The balance for the invoice should be 8,500
Select salesLedger.customerID, salesLedger.invNum, salesLedger.invoiceDate, (salesLedger.payable - sum(payments.allocationAmount)) as thenBalance
FROM salesLedger
LEFT JOIN payments
ON payments.invNum = salesLedger.invNum
WHERE
salesLedger.invoiceDate <= ‘2017/01/16’ and payments.receiptDate <= ‘2017-01-16’
and thenBalance <> 0;
But I am getting unknown column 'thenBalance' error message.
Can anyone advise. Is there a better way of getting the required data ?
J