Hi,

I am trying to create a report that reports the total application by each day in a month. The problem I encounter is that I am not able to get the last record values on sort break as I uses SUM.

Can anyone enlighten me on this?

Snippet of the table APPL as follow :

  
ANUPDT		ANLOPN
01/04/2006	2
01/04/2006	3
02/04/2006	4
02/04/2006	5
02/04/2006	6
...
30/04/2006	45

The output I obtained looks something like this:

  
Day	Total No. of Application	Last Application No	Total Applications
01		2			  	5			3
02		3				15			12
...
30

Expected Result :

  
Day	Total No. of Application	Last Application No	Total Applications
01		2			  	3			1
02		3				6			3
...

Below is a code snipped of my report :

  
SET EMPTYREPORT = ON

FILEDEF DATEMAS DISK DATERNG.MAS
-RUN
-WRITE DATEMAS FILE=DATERNG,SUFFIX=XFOC
-WRITE DATEMAS SEGNAME=SEG1
-WRITE DATEMAS FIELD=DATE_KEY, ,YYMD ,YYMD , $
-RUN
-* Now create it
CREATE FILE DATERNG
-* and add all possible dates within a month
MODIFY FILE DATERNG
FREEFORM DATE_KEY.A8.
LOG FORMAT MSG OFF
LOG TRANS MSG OFF
LOG INVALID MSG OFF
DATA
-* This repeat loop will ensure at least a whole year is input,
-* Any invalid dates will be rejected.
-SET &Year  = &year;
-SET &Month = &month;
-SET &Day   = 1;
-REPEAT :Loop1 31 TIMES;
-SET &Date = IF &Day LT 10 THEN '0' || &Day ELSE &Day;
&Year&Month&Date
-SET &Day = &Day + 1;
-:Loop1
END
-RUN

TABLE FILE DATERNG
PRINT DATE_KEY
ON TABLE HOLD AS DATERANGE
END
-RUN

TABLE FILE DATERANGE
PRINT
* NOPRINT
END
-RUN

-*Card Application File
DEFINE FILE APPL
UPDTCENTURY/I7 = IF ARGLEN(7,EDIT(ANUPDT),UPDTCENTURY) = 6 THEN 0 ELSE IF SUBSTR(7, EDIT(ANUPDT), 0, 1, 1, 'A1') = '1' THEN 1 ELSE 0;
UPDT8/I8 =  IF UPDTCENTURY = 0 THEN 19000000 + ANUPDT ELSE IF UPDTCENTURY = 1 THEN 20000000 + ANUPDT;
UPDT/YYMD = DATECVT(UPDT8, 'I8YYMD', 'I8YYMD');
DATE3/A8YYMD = UPDT;
DD3/A2 = EDIT(DATE3,'$$$$$$99');
MM3/A2 = EDIT(DATE3,'$$$$99$$');
YY3/A4 = EDIT(DATE3,'9999$$$$');
END

TABLE FILE APPL
PRINT
*
UPDT
BY UPDT
BY ANLOPN
ON TABLE HOLD AS HOLD1
END

-*Join Date Range with Card Application File
JOIN
 LEFT_OUTER FILE DATERANGE AT DATE_KEY TO MULTIPLE FILE HOLD1
 AT UPDT TAG J0 AS J0
 END

DEFINE FILE DATERANGE
TOT_APP/I11=IF DATE_KEY EQ UPDT THEN 1 ELSE 0;
MONTH/A2='&month';
YEAR/A4='&year';
DATE3/A8YYMD=UPDT;
DD3/A2=EDIT(DATE3,'$$$$$$99');
MM3/A2=EDIT(DATE3,'$$$$99$$');
YY3/A4=EDIT(DATE3,'9999$$$$');
LAST_NO/I11=IF DATE_KEY EQ UPDT THEN ANLOPN ELSE 0;
END

TABLE FILE DATERANGE
PRINT
TOT_APP
LAST_NO
BY DATE_KEY
BY ANLOPN
WHERE MM3 EQ MONTH AND YY3 EQ YEAR;
ON TABLE HOLD AS HOLD2
END
-RUN

DEFINE FILE HOLD2
MONTH/A2 = '&month';
MTHDESC/A15=DECODE MONTH(01 'JANUARY' 02 'FEBRUARY' 03 'MARCH' 04 'APRIL' 05 'MAY' 06 'JUNE' 07 'JULY' 08 'AUGUST' 09 'SEPTEMBER' 10 'OCTOBER' 11 'NOVEMBER' 12 'DECEMBER');
END
-* Generate Report 
TABLE FILE HOLD2
SUM
     COMPUTE DAYS/A2 = EDIT(DATECVT(DATE_KEY, 'YYMD', 'A8YYMD'), '$$$$$$99');
 AS 'Day'
     'HOLD2.HOLD2.TOT_APP' AS 'Total No. of Application'
     'HOLD2.HOLD2.LAST_NO' AS 'Last Application No.'
     COMPUTE TOT/I11 = LAST_NO - TOT_APP; AS 'Total Application'
BY 'HOLD2.HOLD2.DATE_KEY' NOPRINT
ON TABLE SUBHEAD
" "
" "
HEADING
"DAILY CARD STATISTICS FOR THE MONTH OF <MTHDESC <+0>&year"
" "
"APPLICATION COUNT (AA No.)"
" "
FOOTING
" "
" "
" "
"Private & Confidential"
"Report Generated On <+0>&DATEtrDMYY<+0>    "
ON TABLE SET PAGE-NUM ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

This message has been edited. Last edited by: Kevin Tong,

How about using LST. and CNT.

TABLE FILE HOLD2
SUM
     COMPUTE DAYS/A2 = EDIT(DATECVT(DATE_KEY, 'YYMD', 'A8YYMD'), '$$$$$$99');
 AS 'Day'
     'HOLD2.HOLD2.TOT_APP' AS 'Total No. of Application'
     LST.LAST_NO AS 'Last Application No.'
     CNT.LAST_NO AS 'Total Application'
BY 'HOLD2.HOLD2.DATE_KEY' NOPRINT

Thank you Waz for replying.

I have tried LST., but it will only return the last record value not based on the sort break BY but as a whole at the last record and 0 for others.

  
Day	Total No. of Application	Last Application No	Total Applications
01		2			  	0			0
02		3				0			0
...
30		1				45			44

The use of MAX. solve my issue.

  
TABLE FILE HOLD2
SUM
     COMPUTE DAYS/A2 = EDIT(DATECVT(DATE_KEY, 'YYMD', 'A8YYMD'), '$$$$$$99'); AS 'Day'
     'HOLD2.HOLD2.TOT_APP' AS 'Total No. of Application'
      MAX.LAST_NO AS 'Last Application No.'
     COMPUTE TOT/I11 = MAX.LAST_NO - TOT_APP; AS 'Total Application'
BY 'HOLD2.HOLD2.DATE_KEY' NOPRINT

Thank you Waz for your pointer.

You might try replacing your conditional join with a standard join. You might also be able to avoid creating hold file HOLD2. These changes should allow Waz's suggestion of using the LST. and CNT. prefix operators to work correctly.

-*Card Application File
DEFINE FILE APPL
 UPDTCENTURY/I7 = IF ARGLEN(7,EDIT(ANUPDT),UPDTCENTURY) EQ 6 THEN 0
                  ELSE IF SUBSTR(7, EDIT(ANUPDT), 0, 1, 1, 'A1') EQ '1' THEN 1
                  ELSE 0 ;
 UPDT8/I8 = IF UPDTCENTURY EQ 0 THEN 19000000 + ANUPDT ELSE IF UPDTCENTURY EQ 1 THEN 20000000 + ANUPDT ;
 UPDT/YYMD = DATECVT(UPDT8, 'I8YYMD', 'I8YYMD');
 DATE3/A8YYMD = UPDT;
 MM3/A2 = EDIT(DATE3,'$$$$99$$');
 YY3/A4 = EDIT(DATE3,'9999$$$$');
END

TABLE FILE APPL
 BY UPDT
 BY ANLOPN
 ON TABLE HOLD AS HOLD1
 WHERE ( MM3 EQ '&month' ) AND ( YY3 EQ '&year' );
END

-*Join Date Range with Card Application File
JOIN LEFT_OUTER DATE_KEY IN DATERANGE
  TO MULTIPLE UPDT IN HOLD1 AS J0
END

DEFINE FILE DATERANGE
 MONTH/A2 = '&month';
 MTHDESC/A15 = DECODE MONTH ( 01 'JANUARY' 02 'FEBRUARY' 03 'MARCH' 04 'APRIL' 05 'MAY' 06 'JUNE'
                              07 'JULY' 08 'AUGUST' 09 'SEPTEMBER' 10 'OCTOBER' 11 'NOVEMBER' 12 'DECEMBER');
END

-* Generate Report 
TABLE FILE HOLD2
 SUM
  COMPUTE DAYS/A2 = EDIT(DATECVT(DATE_KEY, 'YYMD', 'A8YYMD'), '$$$$$$99'); AS 'Day'
  CNT.ANLOPN AS 'Total No. of Application'
  LST.ANLOPN AS 'Last Application No.'
  COMPUTE TOT/I11 = (LST.ANLOPN - CNT.ANLOPN); AS 'Total Application'
 BY DATE_KEY NOPRINT
 ON TABLE SUBHEAD
  " "
  " "
 HEADING
  "DAILY CARD STATISTICS FOR THE MONTH OF <MTHDESC <+0>&year"
  " "
  "APPLICATION COUNT (AA No.)"
  " "
 FOOTING
  " "
  " "
  " "
  "Private & Confidential"
  "Report Generated On <+0>&DATEtrDMYY<+0>    "
 ON TABLE SET PAGE-NUM ON
 ON TABLE NOTOTAL
 ON TABLE PCHOLD FORMAT HTML
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

Thanks Dan...It worked like a charm.