""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
USING SQL TO PRINT MULTI-COLUMN AGING REPORTS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
From Bill Downall, 5411 White Willow Court, Indianapolis, IN 46254-
9633. Bill is a database consultant and educator. You can reach him at
317-297-3810, or through David M. Blocker and Associates, at 617-784-
1919.
The January/February 1991 R:BASE EXCHANGE showed you one technique for
producing an aged accounts receivable report--or any report that needs
to print summaries of several rows horizontally across the page. This
article teaches you how to use SQL to do the same thing.
SQL Technique Overview
""""""""""""""""""""""
In this article, you'll use the following SQL techniques to prepare
the data:
o SUM function (one of several SELECT functions) and logical (IFGT
and IFLT) SuperMath functions--you'll use these to create totals
of numeric columns or expressions across a group of rows. SUM
gets the totals while IFGT and IFLT create the groups (print
columns) that will be shown horizontally across the page. The
IFGT and IFLT expressions used here are similar to those given in
the previous article.
o GROUP BY clause of the SELECT command--you'll use GROUP BY to
repeat the same calculations for each client account number.
o Views--you'll use views to save complex SELECT commands and to
give descriptive, meaningful names to the expressions--names that
will appear in subsequent SELECT commands or in the output.
The example described below uses two views. The first view creates an
expression and gives it a column name. The second view names that
pseudo-column within an expression. After creating the views, you'll
use a single SQL SELECT to create the aging report.
Example Table & Data
""""""""""""""""""""
An aged accounts receivable report shows the ages (in days) of amounts
that have been billed but not yet paid.
To make comparison with the previous article easy, I based this SQL
technique on the same table (ARITEMS) and columns: ACCT (account
number), AMOUNT (amount owed), and TDATE (transaction date). Create
the database (AGING) at the Create/Modify menu with one table named
(ARITEMS), with three columns. CREATE TABLE ARITEMS (ACCT integer,
AMOUNT currency, TDATE date) and to load ARITEMS with
data.
You will create two views as explained below.
Step by Step to the Aging Report
""""""""""""""""""""""""""""""""
STEP 1
======
Create the first view (AGING). The variable VREPDATE holds the report
date (as of date). You must set it before creating the view. For
example, you might set it by using this command:
SET VAR vrepdate DATE = ('12/31/90')
Create the view by using this command:
CREATE VIEW aging +
( client, amount, daysout ) AS SELECT +
acct, amount, (.vrepdate - tdate) +
FROM aritems
The view AGING is slightly different from the table ARITEMS. In place
of the transaction date column (TDATE), this view creates a new
pseudo-column (DAYSOUT), an INTEGER defined as the numbers of days
between the report date and transaction date.
The expression that defines DAYSOUT uses the variable VREPDATE. You
could use .#DATE (the current system date) instead of .VREPDATE, but
then you couldn't specify a different report date (ending date or "as
of" date). For example, #DATE is inappropriate for the example data.
The example rows are all dated between May and December 1990.
Therefore, for this example, you need to set VREPDATE to December 31,
1990.
The view definition contains a variable (VREPDATE), so before using
the view, give VREPDATE an initial data type and value. Test the view
by entering this command:
SELECT * FROM aging
The first few lines of output look like this:
client amount daysout
---------- --------------- ---------
101 $73.00 225
102 $146.00 195
103 $219.00 213
104 $292.00 140
101 $22.00 179
STEP 2
======
Create the second view (AGEGROUP) to create and sum the report columns
by using the following command:
CREATE VIEW agegroup +
(Client, Total, Current_, Over_30, +
Over_60, Over_90, Over_120) +
AS SELECT Client, SUM (amount), +
SUM (IFLT(daysout,30,amount,0)), +
SUM (IFGT(daysout,29, +
(IFLT(daysout,60,amount,0)),0)), +
SUM (IFGT(daysout,59,+
(IFLT(daysout,90,amount,0)),0)), +
SUM (IFGT(daysout,89,+
(IFLT(daysout,120,amount,0)),0)), +
SUM (IFGT(daysout,119,amount, 0)) +
FROM aging GROUP BY client
Notice how this view's column structure is similar to that of an aging
report. The column names created in the view will appear as column
headings in a SELECT command. You can't name a column 30_to_60 because
SQL naming conventions require all column names to begin with a
letter, and you can't name a column CURRENT because that's a reserved
word. The underscore character at the end of CURRENT_ makes it
acceptable.
Look at the expression in the definition for the CURRENT_ view column:
SUM (IFLT(daysout,30,amount,0))
In the view AGING, the column DAYSOUT was defined as the number of
days between billing and the report date. Now the SUM(IFLT(...))
expression calculates a zero for any row where DAYSOUT is greater than
or equal to 30. If DAYSOUT is less than 30, the value of the AMOUNT
column is used for the CURRENT_ column in this second view.
Across the entire view, the SELECT function SUM calculates the total.
For example, look at this portion:
SUM (IFLT(daysout,30,amount,0))
It calculates the sum of the AMOUNT column for those rows that are
current (less than 30 days old). If that was the only number you cared
about, it would be easier to get with the following WHERE clause:
SELECT SUM (amount) FROM aritems +
WHERE daysout <= 30
But each SELECT command has only one WHERE clause. In order to build a
single SELECT command that shows one sum for current rows and
different sums for other age groups, you need to use the IF
expressions to do the work of an internal WHERE clause.
SELECT uses the IF expressions to process all the rows in the view,
putting each one in the correct report column grouping and summing
each group.
There's a SUM function for each of the five aging periods. Several of
them use complex IFGT and IFLT functions. For example, look at this
SUM function:
SUM (IFGT(daysout,29,+
(IFLT(daysout,60,amount,0)),0))
The third argument of the IFGT function is another IF function. In
English, it says, "If DAYSOUT is greater than 30, see if it's also
less than 60. If it is, add the value of the AMOUNT column. Otherwise,
add zero."
STEP 3
======
To produce the final report, run a short command file similar to
AGING.CMD (listed below). It initializes VREPDATE with the "as of"
date, and uses a single SELECT based on the second view (AGEGROUP) to
print the report shown below. The SELECT uses "=w" width specifiers to
format the report columns and "=S" to provide grand totals. The
capitalization used for column names in this SELECT is what shows up
in the final report. The following AGING.CMD is an example:
*( AGING.CMD--produced aging report.)
SET VAR vrepdate DATE = ('12/31/90')
FILLIN vrepdate=8 USING +
'Enter report date: ' EDIT REVERSE
*( Insert commands to select print destination)
CLS
WRITE ' Aged +
Accounts Receivable Report:' .vrepdate
WRITE ' ' ; WRITE ' '
SELECT Client=6, Total=10=S, +
Current_=10=S, Over_30=10=S, +
Over_60=10=S, Over_90=10=S, +
Over_120=10=S FROM agegroup
CLEAR VAR vrepdate
RETURN
STEP 4
======
Run AGING.CMD and create the report. Here's an example of the output:
Aged Accounts Receivable Report: 12/31/90
Client Total Current_ Over_30 Over_60 Over_90 Over_120
------ ---------- --------- --------- --------- --------- ----------
101 $178.00 $0.00 $0.00 $0.00 $0.00 $178.00
102 $1,581.85 $0.00 $324.85 $0.00 $0.00 $1,257.00
103 $1,466.85 $470.85 $219.00 $0.00 $329.00 $448.00
104 $2,094.85 $908.85 $292.00 $479.00 $123.00 $292.00
------ ---------- --------- --------- --------- --------- ----------
$5,321.55 $1,379.70 $835.85 $479.00 $452.00 $2,175.00
Further Enhancements
""""""""""""""""""""
To improve the look of the report, set HEADINGS OFF, and use a few
WRITE statements to create your own headings.
To suppress printing zero dollar values, create an R:BASE report
instead of using the final SELECT. Base the report on AGEGROUP, and
use IF expressions to convert the numbers to TEXT values, replacing
the $0.00 values with blanks. Locate the TEXT values on detail (D)
lines.
The view (AGEGROUP) groups the data, and the report prints it. There
will be a longer delay before the report starts to print because the
view must be assembled. But once printing begins, all the summary
totals have already been calculated, so R:BASE has fewer expressions
to calculate while the report prints.