Sample Request: Count Of Promotions By Fiscal Year

The request below can be used to identify the number of promotions (A64 transactions) at your campus for the last 5 fiscal years.  A define (ACADYEAR) was created based on transaction effective date to determine the fiscal year.  Note:  Smart dates must be enclosed in single quotes when used in defines.   

 

EX TR

DEFINE FILE TR ADD

ACADYEAR/A5 = IF TR:EFFDATE GE '07012001' THEN '01/02' ELSE

              IF TR:EFFDATE GE '07012000' THEN '00/01' ELSE

              IF TR:EFFDATE GE '07011999' THEN '99/00' ELSE

              IF TR:EFFDATE GE '07011998' THEN '98/99' ELSE

              IF TR:EFFDATE GE '07011997' THEN '97/98' ELSE 'other';

END

TABLE FILE TR

COUNT TR:TRANCODE

BY ACADYEAR

WHERE TR:TRANCODE EQ 'A64'

WHERE TR:EFFDATE GE 07011997

END