INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

iSeries Printing

running summary by ronze
Posted: 17 Jun 02

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

Back to IBM servers FAQ Index
Back to IBM servers Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close