Last Command

The LAST command is very useful when you need to capture information from the previous record on the data base.  In the example below, the last command is used to identify the difference in base pay rate from the prior employment history transaction.  Note:  SSA is not displayed in the sample report below, but is a sort field in the report request.

The report request to generate this report is done into two parts.  The first part sorts data in the TR file and places the data in a hold file.   The second part calculates the differences in base pay using the LAST command and generates the final report

Part I

By default, transactions in the TR file are sorted in descending date order (high to low).   Before the LAST command can be used, the transactions must first be sorted in ascending date order (low to high) using the command BY HIGHEST TR:LISTCNT.   The field TR:LISTCNT  identifies the correct posting order of the transactions.  The command HIGHEST reverses the sort order.

EX TR  
TABLE FILE TR
PRINT tr:effdate tr:TRANCODE TR:TRANNAME TR:basepay TR:WNAME
BY TR:SSA

BY TR:PSNSEQ

BY highest TR:listcnt
ON TABLE HOLD AS PERMSML
END

Part II

After the data is sorted in the correct order, the LAST command is used in a define (LASTBASE) to retrieve the base pay from the prior record only if the record is for the same SSN and within the same position sequence.  After that value is determined, another field is defined (DIFF) to determine the difference between base pay on the current transaction and the field LASTBASE.  

DEFINE FILE PERMSML ADD
LASTBASE/P12.2M = IF (TR:SSA EQ LAST TR:SSA) And (TR:PSNSEQ EQ LAST TR:PSNSEQ)
              tHEN LAST TR:basepay ELSE 0;
DIFF/P12.2M = IF LASTBASE GT 0 THEN (TR:basepay - LASTBASE) ELSE 0;
END

TABLE FILE permsml
PRINT TR:EFFDATE       as 'eff date'

      TR:TRANCODE      AS 'TRAN'

      TR:TRANNAME/a25  AS 'TYPE'

      TR:basepay/p8.2m AS 'BASE'

      DIFF             AS 'DIFF'
BY TR:WNAME            as 'name' skip-line

BY TR:SSA              as 'ssa'  fold-line
BY TR:PSNSEQ           as 'psn'

BY highest TR:listcnt  NOPRINT
END

The entire report request is available in the common library as LASTCMD.  For more information on the LAST command, refer to your FOCUS documentation or call the Hotline.   

Last Updated: January 2, 2003