An Accounts Receivable report in one query
I have several tables:
Customer: CustID# Name C001 John C002 Marry C003 Scott Product: ProdID# Name P001 Casing P002 Keyboard Sales: NoSales# Date CustID ProdID Amount S001 04/02/01 C001 P001 200 S002 05/14/01 C001 P002 150 S003 05/21/01 C002 P001 200 Receipt: NoReceipt# Date NoSales# Amount R001 04/25/01 S001 70 R002 05/01/01 S001 150 R003 05/28/01 S002 100 R004 05/30/01 S003 120 Account Receivable Report periode 05/01/01 - 05/31/01 : CustID Name Beginning(AR) Sales Receipt Ending(AR) C001 John 130 150 250 30 C002 Marry 0 200 120 80 C003 Scott 0 0 0 0
How do I make this report, with one SQL statement, two parameters (start date and end date)?
Notes:
Format Date : mm/dd/yy
I like how you've related the receipts to the sales. This makes it a bit easier, although it does require left joins, which in turn require the COALESCE function.
Let's start with the Beginning AR amount. This is the sum of all the sales minus all the receipts prior to the start of the period --
select CustID, Customer.Name , sum(coalesce(Sales.Amount,0)) -sum(coalesce(Receipt.Amount,0)) as BeginningAR from Customer left join Sales on Customer.CustID=Sales.CustID left join Receipt on Sales.NoSales=Receipt.NoSales where Sales.Date < #startdate# and Receipt.Date < #startdate# group by CustID, Customer.Name
Note that I included a GROUP BY in order to make this (standalone) query work properly; more on this below.
The Ending AR amount is the sum of all the sales minus all the receipts up to and including the end of the period --
select CustID, Customer.Name , sum(coalesce(Sales.Amount,0)) -sum(coalesce(Receipt.Amount,0)) as EndingAR from Customer left join Sales on Customer.CustID=Sales.CustID left join Receipt on Sales.NoSales=Receipt.NoSales where Sales.Date <= #enddate# and Receipt.Date <= #enddate# group by CustID, Customer.Name
The sales and receipts for the period are similar, and are obtained in separate columns --
select CustID, Customer.Name , sum(coalesce(Sales.Amount,0)) as SalesAmt , sum(coalesce(Receipt.Amount,0)) as ReceiptAmt from Customer left join Sales on Customer.CustID=Sales.CustID left join Receipt on Sales.NoSales=ReceiptNoSales where Sales.Date between #startdate# and #enddate# and Receipt.Date between #startdate# and #enddate# group by CustID, Customer.Name
Okay, now to put them all into one query, you could use this structure in standard SQL (SQL-92? SQL-99? I cannot remember what this structure is called, but it lets you put a subselect into the select list) --
select CustID, Customer.Name , ( select sum(coalesce(Sales.Amount,0)) -sum(coalesce(Receipt.Amount,0)) from Customer left join Sales on Customer.CustID=Sales.CustID left join Receipt on Sales.NoSales=Receipt.NoSales where CustID = XX.CustID and Sales.Date < #startdate# and Receipt.Date < #startdate# ) as BeginningAR , sum(coalesce(Sales.Amount,0)) as SalesAmt , sum(coalesce(Receipt.Amount,0)) as ReceiptAmt , ( select sum(coalesce(Sales.Amount,0)) -sum(coalesce(Receipt.Amount,0)) from Customer left join Sales on Customer.CustID=Sales.CustID left join Receipt on Sales.NoSales=Receipt.NoSales where CustID = XX.CustID and Sales.Date <= #enddate# and Receipt.Date <= #enddate# ) as EndingAR from Customer XX left join Sales on Customer.CustID=Sales.CustID left join Receipt on Sales.NoSales=ReceiptNoSales where Sales.Date between #startdate# and #enddate# and Receipt.Date between #startdate# and #enddate# group by CustID, Customer.Name
Notice how the GROUP BY clauses are not part of the subselects, because the subselects are correlated to the Customer row of the outer query.
If that, um, structure is not available in your database system, you can achieve the same effect by converting the subselects to views and then joining Customer to the views with inner joins.
For More Information
- What do you think about this answer? E-mail us at [email protected] with your feedback.