SQL to return balances
Posted on 2006-04-14
im trying to write a script that declares and sets a variable that’s equal to the total outstanding balance due. If that balance due is greater than $10,000.00, the script should return a result set consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance for each invoice with a balance due, sorted with the oldest due date first. If the total outstanding balance due is less than $10,000.00, return the message “Balance due is less than $10,000.00.”
i have this so far but it does not return the individual balances by looking at the declare variable.
Delcare @totbaldue money
set @totbaldue = (select sum(invoicetotal-paymenttotal-credittotal) from invoices)
select vendorname, invoicenumber, invoicedate, @totbaldue as balance
from vendors join invoices on vendors.vendoris=invoices.vendorid