Hi

I am facing issue while querying hold file after reading some values in a loop. I get 0 records , seems like -RUN and READFILE clears all records in previous file.

Below is the code and I am getting 0 count for HLD_COUNT1 and HLD_COUNT2 as they are used in last join.

Code:

I am calculation Terminated counts in 3 different date ranges and average of Active employees at month end between 3 different time ranges

-SET &ECHO='ALL';
-DEFAULTH &FROM_DT1 = '2018/01/01';
-DEFAULTH &TO_DT1 = '2018/03/25' ;
-DEFAULTH &FROM_DT2 = '2016/08/01';
-DEFAULTH &TO_DT2 = '2016/10/23' ;
-DEFAULTH &FROM_DT3 = '2015/09/01';
-DEFAULTH &TO_DT3 = '2015/11/23' ;
-DEFAULTH &RGLTRY_RGN = '_FOC_NULL';
-DEFAULTH &FIN_HIER_ROLLUP = '_FOC_NULL';
-DEFAULTH &ACTV_CNT ='';
-DEFAULTH &TOTACTVCNT = '';
-DEFAULTH &TRNOVER1 = '';

-SET &I1 = DATEDIF(&FROM_DT1, &TO_DT1, 'M');

-SET &EFF_DT1 = EDIT(&FROM_DT1, '9999') | EDIT(&FROM_DT1, '$$$$$99$$$')| EDIT(&FROM_DT1, '$$$$$$$$99');
-SET &EFF_DT2 = EDIT(&TO_DT1, '9999') | EDIT(&TO_DT1, '$$$$$99$$$')| EDIT(&TO_DT1, '$$$$$$$$99');
-SET &DT_LAST1 = DATECVT(( (DATECVT(&EFF_DT2,'I8YYMD','YYMD'))),'YYMD','I8YYMD');
-SET &DT_EOM1 = DATECVT(( DATEMOV((DATECVT(&EFF_DT1,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &DT_EOM1
-SET &DTCHK1 = &DT_EOM1;

-SET &I2 = DATEDIF(&FROM_DT2, &TO_DT2, 'M');

-SET &EFF_DT3 = EDIT(&FROM_DT2, '9999') | EDIT(&FROM_DT2, '$$$$$99$$$')| EDIT(&FROM_DT2, '$$$$$$$$99');
-SET &EFF_DT4 = EDIT(&TO_DT2, '9999') | EDIT(&TO_DT2, '$$$$$99$$$')| EDIT(&TO_DT2, '$$$$$$$$99');
-SET &DT_LAST2 = DATECVT(( (DATECVT(&EFF_DT4,'I8YYMD','YYMD'))),'YYMD','I8YYMD');
-SET &DT_EOM2 = DATECVT(( DATEMOV((DATECVT(&EFF_DT3,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &DT_EOM2
-SET &DTCHK2 = &DT_EOM2;

-SET &I3 = DATEDIF(&FROM_DT3, &TO_DT3, 'M');

-SET &EFF_DT5 = EDIT(&FROM_DT3, '9999') | EDIT(&FROM_DT3, '$$$$$99$$$')| EDIT(&FROM_DT3, '$$$$$$$$99');
-SET &EFF_DT6 = EDIT(&TO_DT3, '9999') | EDIT(&TO_DT3, '$$$$$99$$$')| EDIT(&TO_DT3, '$$$$$$$$99');
-SET &DT_LAST3 = DATECVT(( (DATECVT(&EFF_DT6,'I8YYMD','YYMD'))),'YYMD','I8YYMD');
-SET &DT_EOM3 = DATECVT(( DATEMOV((DATECVT(&EFF_DT5,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &DT_EOM3
-SET &DTCHK3 = &DT_EOM3;

JOIN
INNER HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.CCR_CDE IN hrad_emplt_hist TO UNIQUE
HRAD_GL_HIER.HRAD_GL_HIER.CCR_CDE IN hrad_gl_hier TAG J0 AS J0
END


TABLE FILE HRAD_EMPLT_HIST
BY LOWEST HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EMP_ID
BY LOWEST HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT
BY LOWEST 1 HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EFF_DT
BY LOWEST 1 HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EFF_SEQ
WHERE ( HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT GE &FROM_DT1.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT LE &TO_DT1.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.PAY_GRP NE 'DIR' OR 'DCP' OR 'CDR';
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TNRE_AT_TERM LE 1.00
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.RGLTRY_RGN EQ &RGLTRY_RGN.(OR(FIND HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.RGLTRY_RGN IN HRAD_EMPLT_HIST|FORMAT=A5)).Country:.;
WHERE (( J0.HRAD_GL_HIER.ORG_LVL_2_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_3_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_4_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_5_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_6_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_7_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_8_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_9_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_10_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_11_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_12_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_13_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_14_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_15_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_16_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_17_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_18_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_19_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_20_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ));
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_TERMS11
END

TABLE FILE HLD_TERMS11
SUM
CNT.EMP_ID
COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS HLD_TERMS1
END



-REPEAT LOOP1 FOR &I1 FROM 0 TO &I1;

DEFINE FILE HRAD_JOB_SUMMARY
MTH_END/YYMD = &DT_EOM1;
MONTHEND/YYMD = IF MTH_END GE &DT_LAST1 THEN &DT_LAST1 ELSE &DT_EOM1;
END

TABLE FILE HRAD_JOB_SUMMARY
BY HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EMP_ID
BY MONTHEND
BY HIGHEST 1 HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT
BY HIGHEST 1 HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_SEQ
WHERE ( HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT GE &FROM_DT1.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT LE &TO_DT1.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE MTH_END GE EFF_DT ;

ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HLD_ACTIVE FORMAT SQL_SCRIPT
END


JOIN
EMP_ID AND EFF_DT AND EFF_SEQ IN
HRAD_EMPLT_HIST TO UNIQUE EMP_ID AND EFF_DT AND EFF_SEQ IN HLD_ACTIVE TAG J1 AS J1
END

DEFINE FILE HRAD_EMPLT_HIST
MTH_END/YYMD = &DT_EOM1;
MONTHEND/YYMD = IF MTH_END GE &DT_LAST1 THEN &DT_LAST1 ELSE &DT_EOM1;
ACTV_CNT/I10 = IF EMP_STATUS EQ 'A' OR 'L' OR 'P' OR 'S' THEN 1 ELSE 0;
TENURE/D20.2 = DATEDIF(SRVC_DT,MONTHEND, 'Y');
END

TABLE FILE HRAD_EMPLT_HIST
BY EMP_ID
BY MONTHEND
BY HIGHEST 1 EFF_DT
BY EFF_DT NOPRINT
BY EFF_SEQ
BY EMP_STATUS
BY ACTV_CNT
BY TENURE
WHERE ( EFF_DT GE &FROM_DT1.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( EFF_DT LE &TO_DT1.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE MONTHEND GE EFF_DT;
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_ACTCOUNT
END

TABLE FILE HLD_ACTCOUNT
SUM
ACTV_CNT
BY MONTHEND
WHERE EMP_STATUS EQ 'A' OR 'L' OR 'P' OR 'S';
WHERE TENURE LE 1.00;
ON TABLE HOLD AS HLD_AVGCNT FORMAT BINARY
END

-RUN
-READFILE HLD_AVGCNT
-TYPE &ACTV_CNT
-SET &TOTACTVCNT = &TOTACTVCNT + &ACTV_CNT;

-SET &DTCHK1 = DATECVT((DATEMOV(DATEADD((DATECVT(&DT_EOM1,'I8YYMD','YYMD')),'M', 1),'EOM')),'YYMD','A8YYMD');
-SET &DT_EOM1 = &DTCHK1;
-TYPE &DTCHK1;
-LOOP1
END


DEFINE FILE HLD_AVGCNT
TOTALCNT/I10 = &TOTACTVCNT;
MNTHCNT/I10 =&I1;
END
TABLE FILE HLD_AVGCNT
PRINT
TOTALCNT
MNTHCNT
ACTV_CNT NOPRINT
COMPUTE AVG_CNT1/I10 = TOTALCNT/MNTHCNT;
COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS HLD_ACTFIN1
END


JOIN HLD_TERMS1.HLD_TERM.DUMMY IN HLD_TERMS1 TO MULTIPLE DUMMY IN HLD_ACTFIN1 TAG J2 AS J2;


TABLE FILE HLD_TERMS1
PRINT

EMP_ID AS 'TERMS'
AVG_CNT1 AS 'ACTIVES'
COMPUTE TRNOVER1/D20% = (EMP_ID/AVG_CNT) * 100 ;
DUMMY
ON TABLE HOLD AS HLD_COUNT1
END



-*second time duration calculation


TABLE FILE HRAD_EMPLT_HIST
BY LOWEST HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EMP_ID
BY LOWEST HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT
BY LOWEST 1 HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EFF_DT
BY LOWEST 1 HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EFF_SEQ
WHERE ( HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT GE &FROM_DT2.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT LE &TO_DT2.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.PAY_GRP NE 'DIR' OR 'DCP' OR 'CDR';
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TNRE_AT_TERM LE 1.00
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.RGLTRY_RGN EQ &RGLTRY_RGN.(OR(FIND HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.RGLTRY_RGN IN HRAD_EMPLT_HIST|FORMAT=A5)).Country:.;
WHERE (( J0.HRAD_GL_HIER.ORG_LVL_2_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_3_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_4_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_5_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_6_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_7_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_8_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_9_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_10_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_11_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_12_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_13_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_14_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_15_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_16_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_17_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_18_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_19_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_20_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ));
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_TERMS22
END
TABLE FILE HLD_TERMS22
SUM
CNT.EMP_ID
COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS HLD_TERMS2
END



-REPEAT LOOP2 FOR &I2 FROM 0 TO &I2;

DEFINE FILE HRAD_JOB_SUMMARY
MTH_END/YYMD = &DT_EOM2;
MONTHEND/YYMD = IF MTH_END GE &DT_LAST2 THEN &DT_LAST2 ELSE &DT_EOM2;
END

TABLE FILE HRAD_JOB_SUMMARY
BY HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EMP_ID
BY MONTHEND
BY HIGHEST 1 HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT
BY HIGHEST 1 HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_SEQ
WHERE ( HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT GE &FROM_DT2.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT LE &TO_DT2.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE MTH_END GE EFF_DT ;

ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HLD_ACTIVE1 FORMAT SQL_SCRIPT
END


JOIN
EMP_ID AND EFF_DT AND EFF_SEQ IN
HRAD_EMPLT_HIST TO UNIQUE EMP_ID AND EFF_DT AND EFF_SEQ IN HLD_ACTIVE1 TAG J3 AS J3
END

DEFINE FILE HRAD_EMPLT_HIST
MTH_END/YYMD = &DT_EOM2;
MONTHEND/YYMD = IF MTH_END GE &DT_LAST2 THEN &DT_LAST2 ELSE &DT_EOM2;
ACTV_CNT/I10 = IF EMP_STATUS EQ 'A' OR 'L' OR 'P' OR 'S' THEN 1 ELSE 0;
TENURE/D20.2 = DATEDIF(SRVC_DT,MONTHEND, 'Y');
END

TABLE FILE HRAD_EMPLT_HIST
BY EMP_ID
BY MONTHEND
BY HIGHEST 1 EFF_DT
BY EFF_DT NOPRINT
BY EFF_SEQ
BY EMP_STATUS
BY ACTV_CNT
BY TENURE
WHERE ( EFF_DT GE &FROM_DT2.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( EFF_DT LE &TO_DT2.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE MONTHEND GE EFF_DT;
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_ACTCOUNT
END

TABLE FILE HLD_ACTCOUNT
SUM
ACTV_CNT
BY MONTHEND
WHERE EMP_STATUS EQ 'A' OR 'L' OR 'P' OR 'S';
WHERE TENURE LE 1.00;
ON TABLE HOLD AS HLD_AVGCNT
END



-RUN

-READFILE HLD_AVGCNT
-TYPE &ACTV_CNT
-SET &TOTACTVCNT = &TOTACTVCNT + &ACTV_CNT;

-SET &DTCHK2 = DATECVT((DATEMOV(DATEADD((DATECVT(&DT_EOM2,'I8YYMD','YYMD')),'M', 1),'EOM')),'YYMD','A8YYMD');
-SET &DT_EOM2 = &DTCHK2;
-TYPE &DTCHK2;

-LOOP2



DEFINE FILE HLD_AVGCNT2
TOTALCNT2/I10 = &TOTACTVCNT;
MNTHCNT/I10 =&I2;
END
TABLE FILE HLD_AVGCNT2
PRINT
ACTV_CNT NOPRINT
TOTALCNT2
MNTHCNT
COMPUTE AVG_CNT2/I10 = TOTALCNT2/MNTHCNT;
COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS HLD_ACTFIN2
END


JOIN
HLD_TERMS2.HLD_TERM.DUMMY IN HLD_TERMS2 TO MULTIPLE HLD_ACTFIN2.HLD_ACTF.DUMMY
IN HLD_ACTFIN2 TAG J4 AS J4
END

TABLE FILE HLD_TERMS2
PRINT
EMP_ID
AVG_CNT2
COMPUTE TRNOVER2/D20% = (EMP_ID/AVG_CNT2) * 100 ;
DUMMY
ON TABLE HOLD AS HLD_COUNT2
END



-*third time duration calculation


TABLE FILE HRAD_EMPLT_HIST
BY LOWEST HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EMP_ID
BY LOWEST HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT
BY LOWEST 1 HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EFF_DT
BY LOWEST 1 HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.EFF_SEQ
WHERE ( HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT GE &FROM_DT2.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TRMNTN_DT LE &TO_DT2.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.PAY_GRP NE 'DIR' OR 'DCP' OR 'CDR';
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.TNRE_AT_TERM LE 1.00
WHERE HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.RGLTRY_RGN EQ &RGLTRY_RGN.(OR(FIND HRAD_EMPLT_HIST.HRAD_EMPLT_HIST.RGLTRY_RGN IN HRAD_EMPLT_HIST|FORMAT=A5)).Country:.;
WHERE (( J0.HRAD_GL_HIER.ORG_LVL_2_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_3_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_4_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_5_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_6_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_7_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_8_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_9_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_10_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_11_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_12_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_13_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_14_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_15_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_16_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_17_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_18_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_19_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ) OR ( J0.HRAD_GL_HIER.ORG_LVL_20_ID EQ &FIN_HIER_ROLLUP.(|FORMAT=A10).Financial Rollup:.QUOTEDSTRING ));
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_TERMS33
END
TABLE FILE HLD_TERMS33
SUM
CNT.EMP_ID
COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS HLD_TERMS3
END


-REPEAT LOOP3 FOR &I3 FROM 0 TO &I3;

DEFINE FILE HRAD_JOB_SUMMARY
MTH_END/YYMD = &DT_EOM3;
MONTHEND/YYMD = IF MTH_END GE &DT_LAST3 THEN &DT_LAST3 ELSE &DT_EOM3;
END

TABLE FILE HRAD_JOB_SUMMARY
BY HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EMP_ID
BY MONTHEND
BY HIGHEST 1 HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT
BY HIGHEST 1 HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_SEQ
WHERE ( HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT GE &FROM_DT3.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( HRAD_JOB_SUMMARY.HRAD_JOB_SUMMARY.EFF_DT LE &TO_DT3.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE MTH_END GE EFF_DT ;
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HLD_ACTIVE3 FORMAT SQL_SCRIPT
END


JOIN
EMP_ID AND EFF_DT AND EFF_SEQ IN
HRAD_EMPLT_HIST TO UNIQUE EMP_ID AND EFF_DT AND EFF_SEQ IN HLD_ACTIVE3 TAG J5 AS J5
END

DEFINE FILE HRAD_EMPLT_HIST
MTH_END/YYMD = &DT_EOM3;
MONTHEND/YYMD = IF MTH_END GE &DT_LAST3 THEN &DT_LAST3 ELSE &DT_EOM3;
ACTV_CNT/I10 = IF EMP_STATUS EQ 'A' OR 'L' OR 'P' OR 'S' THEN 1 ELSE 0;
TENURE/D20.2 = DATEDIF(SRVC_DT,MONTHEND, 'Y');
END

TABLE FILE HRAD_EMPLT_HIST
BY EMP_ID
BY MONTHEND
BY HIGHEST 1 EFF_DT
BY EFF_DT NOPRINT
BY EFF_SEQ
BY EMP_STATUS
BY ACTV_CNT
BY TENURE
WHERE ( EFF_DT GE &FROM_DT3.(|FORMAT=YYMD).From Date:.QUOTEDSTRING ) AND ( EFF_DT LE &TO_DT3.(|FORMAT=YYMD).To Date:.QUOTEDSTRING );
WHERE MONTHEND GE EFF_DT;
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_ACTCOUNT3
END

TABLE FILE HLD_ACTCOUNT3
SUM
ACTV_CNT
BY MONTHEND
WHERE EMP_STATUS EQ 'A' OR 'L' OR 'P' OR 'S';
WHERE TENURE LE 1.00;
ON TABLE HOLD AS HLD_AVGCNT3 FORMAT BINARY
END

-RUN
-READFILE HLD_AVGCNT3
-TYPE &ACTV_CNT
-SET &TOTACTVCNT = &TOTACTVCNT + &ACTV_CNT;

-SET &DTCHK3 = DATECVT((DATEMOV(DATEADD((DATECVT(&DT_EOM3,'I8YYMD','YYMD')),'M', 1),'EOM')),'YYMD','A8YYMD');
-SET &DT_EOM3 = &DTCHK3;
-TYPE &DTCHK3;
-LOOP3


DEFINE FILE HLD_AVGCNT3
TOTALCNT3/I10 = &TOTACTVCNT;
MNTHCNT/I10 =&I3;
END
TABLE FILE HLD_AVGCNT3
PRINT
ACTV_CNT NOPRINT
TOTALCNT3
MNTHCNT
COMPUTE AVG_CNT3/I10 = TOTALCNT3/MNTHCNT;
COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS HLD_ACTFIN3
END


JOIN
HLD_TERMS3.HLD_TERM.DUMMY IN HLD_TERMS3 TO HLD_ACTFIN3.HLD_ACTF.DUMMY
IN HLD_ACTFIN3 TAG J6 AS J6
END

TABLE FILE HLD_TERMS3
PRINT
EMP_ID
AVG_CNT
COMPUTE TRNOVER3/D20% = (EMP_ID/AVG_CNT3) * 100 ;
DUMMY
ON TABLE HOLD AS HLD_COUNT3
END



JOIN
DUMMY IN HLD_COUNT1 TO DUMMY IN HLD_COUNT2 TAG J7 AS J7
END

JOIN
DUMMY IN HLD_COUNT1 TO DUMMY IN HLD_COUNT3 TAG J8 AS J8
END


TABLE FILE HLD_COUNT1
PRINT
TRNOVER
TRNOVER2
TRNOVER3
END

This message has been edited. Last edited by: FP Mod Chuck,

Time to learn about dialogue manager commands and how -RUN should be used.

Get into the habit of using -RUN after every TABLE FILE filename ... END

e.g.
TABLE FILE CAR
SUM RCOST
BY COUINTRY
END
-RUN

If you haven't had basic training then I would strongly recommend it.

T