running summary

I've gotten a lot of help from this forum and want drop a trick that can be used when developing queries with the AS/400.


here is a detail of how to create a running summary report of totals and counts by date.

first create your file, here is part of mine

Seq Field
10 COM (COMPANY IDENTIFIER)
20 COL (EMPLOYEE FIELD)
30 DRCMDY (TRANSACTION DATE FIELD - running sum)
40 D# (ACCOUNT NUMBER)
50 MMDY (RECEIVED DATE FIELD)
60 DDOWNP (ORIGINAL AMOUNT)
70 DOWNDT (TRANSACTION DATE -diff conf then DRCMDY)
80 TRAMT (TRANSACTION AMOUNT)
90 FEE (COMMISSION ON SALE)

I performed the following aggregate operations

5 D#
MMDY
1 DDOWNP
DOWNDT
1 HTRAMT
1 HCMFEE


this file is called COLPAY1F

Second, Specify this file twice

File . . . . . . . . . COLPAY1F
Library . . . . . . OPERATIONS
Member . . . . . . . . *FIRST
Format . . . . . . . . COLPAY1F
File ID . . . . . . . T01

File . . . . . . . . . COLPAY1F
Library . . . . . . OPERATIONS
Member . . . . . . . . *FIRST
Format . . . . . . . . COLPAY1F
File ID . . . . . . . T02


Third, since I am looking for the running summary per date specify the join crits below:

Type of join . . . . . . . . 1

and

Field Test Field
T01.COL EQ T02.COL
T01.DRCMDY GE T02.DRCMDY
T01.BREAKLVL EQ T02.BREAKLVL
T01.COM EQ T02.COM

notice the date field from T01 is looking for all date on T02 greater than and equal to itself. this is the way you do it.

Fourth, choose the following aggregate options

5 T02.D#
1 T02.D#05
T02.DDOWNP01
1 T02.HTRAMT01
1 T02.HCMFEE01



your physical file will look like this

COM COL DATE CTRL# TRAMT FEE D# D#05 TRAMT01 FEE01
CNT TTL TTL CNT TTL TTL TTL

COM EMP 50,602 1 106.22 47.80 1 1 106.22 47.80
COM EMP 50,902 2 367.58 147.79 2 3 473.80 195.59
COM EMP 51,002 1 100.00 35.00 3 4 573.80 230.59
COM EMP 51,302 1 154.06 77.03 4 5 727.86 307.62
COM EMP 51,702 1 59.37 26.72 5 6 787.23 334.34
COM EMP 52,002 1 167.01 83.51 6 7 954.24 417.85
COM EMP 52,102 1 106.22- .00 7 8 848.02 417.85
COM EMP 52,202 1 223.00 100.35 8 9 1,071.02 518.20
COM EMP 52,802 2 325.60 120.24 9 11 1,396.62 638.44
COM EMP 53,002 1 100.00 40.00 10 12 1,496.62 678.44

Good luck
Christina