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 editor@searchDatabase.com with your feedback.