Retrieving Data With SQL › Handling Null Values at Total Time

Handling Null Values at Total Time

You can enhance the previous example to produce weekly summary information for each product. You also want to print the average number of units sold per day. The final report should look like this:

Report Output

REPORT 01            WEEKLY SALES BY PRODUCT               PAGE 1

       PRODUCT               SALES_DATE           UNITS_SOLD

  Clothes Driers             yyyy-mm-dd                 3
  Clothes Driers             yyyy-mm-dd          ───null---
  Clothes Driers             yyyy-mm-dd          ───null---
  Clothes Driers             yyyy-mm-dd                 6
  Clothes Driers             yyyy-mm-dd                 3
                                                 ───────────
                             Total Weekly Sales        12
                             Average Daily Sales     4.00

  Stoves                     yyyy-mm-dd          ───null---
  Stoves                     yyyy-mm-dd          ───null---
  Stoves                     yyyy-mm-dd          ───null---
  Stoves                     yyyy-mm-dd          ───null---
  Stoves                     yyyy-mm-dd          ───null---
                                                 ───────────
                             Total Weekly Sales  ───null---
                             Average Daily Sales ───null---

  Washing Machines           yyyy-mm-dd                14
  Washing Machines           yyyy-mm-dd          ───null---
  Washing Machines           yyyy-mm-dd                 0
  Washing Machines           yyyy-mm-dd                 3
  Washing Machines           yyyy-mm-dd                12
                                                 ───────────
                             Total Weekly Sales        29
                             Average Daily Sales     7.25

WHERE Clause

First, add a WHERE clause to select data for one week:

 col. 2
 ▼
 SQL SELECT * FROM SALES
*    WHERE SALES_DATE >= 'yyyy-mm-dd' AND
*          SALES_DATE <= 'yyyy-mm-dd';

Subtotals

Next, add a title parameter and a SORT parameter to request subtotals by product. A COUNT field has been initialized to one. It will be used to determine how many detail records have been processed for each group. You can automatically accumulate subtotals for COUNT and UNITS_SOLD_NULL_IND if they appear on detail lines. However, since you don't want the detail values to print, use print position zero:

col. 2
▼
01SORT  PRODUCT 0, SALES_DATE
010 COUNT  1
013 WEEKLY SALES BY PRODUCT
0151*000   COUNT
0151*000   UNITS_SOLD_NULL_IND

Based on the information in the report shown above, the total-time values for PRODUCT, COUNT, and UNITS_SOLD_NULL_IND are:

Subtotal Values(Error: Don't know what to do with column width of "20," (3).(Error: Don't know what to do with column width of "11," (3).

These values let you determine how many UNITS_SOLD for each product are not null. This in turn provides you with the divisor you need to compute the average daily sales for each product. The total-time logic is shown below:

Total-time Logic

col. 2
▼
010 NOT_NULL_COUNTER  0
010 AVERAGE           0.00
018     IF LEVL = 2 DROP
018     COMPUTE COUNT + UNITS_SOLD_NULL_IND  NOT_NULL_COUNTER
018     IF NOT_NULL_COUNTER = 0  400
018     COMPUTE UNITS_SOLD / NOT_NULL_COUNTER  AVERAGE
018     TAKE (1,2,3)
018400  TAKE (1,4,5)
0161*003   '-----------'
0162*002   'Total Weekly Sales'
0162*003   UNITS_SOLD    SZ=6  F-
0163*002   'Average Daily Sales'
0163*003   AVERAGE       SZ=6  F-
0164*002   'Total Weekly Sales'
0164*003   '---null---'
0165*002   'Average Daily Sales'
0165*003   '---null---'