I have a request for a new report and I am confused as to how I can pull the requested information. Here is the descrption.

Here is the sample data from database.

VENDOR DOCUMENT AMOUNT Ca_Date CA_USR CA_ACTION
ORICA 2151524 6025.99 2009/03/03 SCANNER WAITING
ORICA 2151524 6025.99 2009/03/03 LANCASTEMK WAITING
ORICA 2151524 6025.99 2009/03/05 RWCOOK WAITING
ORICA 2151524 6025.99 2009/03/08 RWATKINS APPROVED

We need to calculate the number of days it took to go from one user to the next user. For eg:

Ca_Date CA_USR CA_ACTION
2009/03/03 SCANNER WAITING
2009/03/03 LANCASTEMK WAITING
2009/03/05 RWCOOK WAITING
2009/03/08 RWATKINS APPROVED

It took 0 days to go from SCANNER to LANCASTEMK
It took 2 days to go from LANCASTEMK to RWCOOK
It took 3 days to go from RWCOOK to RWATKINS.

How can I accomplish this?

This message has been edited. Last edited by: Kerry,

Here is a working example:

FILEDEF DATAMAST DISK DATA1.MAS
-RUN

-WRITE DATAMAST
-WRITE DATAMAST FILE=DATA1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=VENDOR   , ALIAS=E01, USAGE=A5    , ACTUAL=A5, $
-WRITE DATAMAST   FIELD=FILL1    , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=DOCUMENT , ALIAS=E02, USAGE=A7    , ACTUAL=A7, $
-WRITE DATAMAST   FIELD=FILL2    , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=AMOUNT   , ALIAS=E03, USAGE=P10.2 , ACTUAL=A7, $
-WRITE DATAMAST   FIELD=FILL3    , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=CA_DATE  , ALIAS=E04, USAGE=A8YYMD, ACTUAL=A8, $
-WRITE DATAMAST   FIELD=FILL4    , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=CA_USR   , ALIAS=E05, USAGE=A10   , ACTUAL=A10, $
-WRITE DATAMAST   FIELD=FILL5    , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=CA_ACTION, ALIAS=E06, USAGE=A8    , ACTUAL=A8, $

FILEDEF DATA1 DISK DATA1.FTM
-RUN

-WRITE DATA1 ORICA 2151524 6025.99 20090303 SCANNER    WAITING
-WRITE DATA1 ORICA 2151524 6025.99 20090303 LANCASTEMK WAITING
-WRITE DATA1 ORICA 2151524 6025.99 20090305 RWCOOK     WAITING
-WRITE DATA1 ORICA 2151524 6025.99 20090308 RWATKINS   APPROVED
-RUN

TABLE FILE DATA1
PRINT
AMOUNT

BY VENDOR
BY DOCUMENT
BY CA_DATE
BY CA_USR
BY CA_ACTION

ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

TABLE FILE H001
PRINT
COMPUTE CA_DATE_PREV/YYMD = LAST CA_DATE; NOPRINT
COMPUTE CA_DATE_CURR/YYMD = CA_DATE; NOPRINT
COMPUTE DAYS_TO_GO/P4     = IF CA_DATE_PREV EQ '' THEN 0 ELSE DATEDIF(CA_DATE_PREV, CA_DATE_CURR, 'D'); NOPRINT
COMPUTE CA_USR_PREV/A10   = LAST CA_USR; NOPRINT

COMPUTE COMMENT/A100 = 'It took ' | PTOA(DAYS_TO_GO, '(P4)', 'A4') | ' days to go from ' | CA_USR_PREV | ' to ' | CA_USR;

WHERE TOTAL CA_USR_PREV NE '';

BY VENDOR
BY DOCUMENT
END
-RUN