-SET &MYSTAMP = &YYMD; WHERE APPROVAL_DATE GE &MYSTAMP-2; WHERE APPROVAL_DATE LE &MYSTAMP;
quote:Use this In Define Section-
DEFINE FILE TABLE1
YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ;
LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ;
FDATEYST/A8YYMD=YST ;
FDATELST2/A8YYMD=LST2 ;
END
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
DEFINE FILE OFFNSDAT YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ; LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ; FDATEYST/A8YYMD=YST ; FDATELST2/A8YYMD=LST2 ; END TABLE FILE OFFNSDAT HEADING CENTER "&SUB_ASG" PRINT UPLASTNAME AS '' APPROVAL_DATE BY RANK_ORDER NOPRINT BY RANK_ABRV AS '' WHERE TSDIVISION EQ '&DIVISION'; WHERE SUB_ASSIGNMENT EQ '&SUB_ASG'; WHERE RANK_ABRV NE 'CPT' OR 'LT'; WHERE APPROVAL_DATE EQ FDATEYST; WHERE APPROVAL_DATE EQ FDATELST2; -*ON TABLE SET EMPTYREPORT ON ON TABLE HOLD FORMAT HTML AS 'X1' ON TABLE SET PAGE-NUM OFF ON TABLE SET HTMLCSS ON ON TABLE SET STYLE *
quote:Originally posted by QuickLearner:<snip> WHERE APPROVAL_DATE EQ FDATEYST; WHERE APPROVAL_DATE EQ FDATELST2; <snip>
quote:Originally posted by QuickLearner:
WHERE APPROVAL_DATE EQ FDATEYST;
WHERE APPROVAL_DATE EQ FDATELST2;
-* File District_Org_Chart.fex -SET &ECHO=ALL; -* -*-DEFAULT &DIVISION = '1D' ; -* DEFAULT &SUB_ASG = 'SHIFT 1' ; ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR SQL SQLORA select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER, INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE, INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE, OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID, EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A' and ROWNUM <= 10; -*TABLE FILE SQLOUT -*PRINT * -*ON TABLE HOLD FORMAT ALPHA -*END TABLE ON TABLE HOLD AS OFFNSDAT END -*RUN -*EXIT -RUN TABLE FILE OFFNSDAT SUM INCIDENT_SUPPLEMENTS.APPROVAL_DATE INCIDENTS.INC_REPORT_NUMBER INCIDENT_SUPPLEMENTS.CREATOR_ID INCIDENT_TYPE_CODES.DESCRIPTION OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE INCIDENTS.INCIDENT_ID INCIDENTS.ITC_CODE OFFENSES.INC_INCIDENT_ID EMPLOYEES.LOGIN_ID EMP_SERVICE_HISTORIES.EJS_EMP_ID EMPLOYEES.EJS_EMP_ID EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE BY OFFICER_ID ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID -RUN -*?FF HOLD -*EXIT -*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1 -*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1 -*-SET &MYSTAMP = &YYMD; JOIN OFFICER_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1 DEFINE FILE OFFNSDAT YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ; LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ; FDATEYST/A8YYMD=YST ; FDATELST2/A8YYMD=LST2 ; END TABLE FILE OFFNSDAT HEADING CENTER "&SUB_ASG" PRINT UPLASTNAME AS '' APPROVAL_DATE BY RANK_ORDER NOPRINT BY RANK_ABRV AS '' WHERE TSDIVISION EQ '&DIVISION'; WHERE SUB_ASSIGNMENT EQ '&SUB_ASG'; WHERE RANK_ABRV NE 'CPT' OR 'LT'; WHERE APPROVAL_DATE LE FDATEYST; WHERE APPROVAL_DATE GE FDATELST2; -*ON TABLE SET EMPTYREPORT ON ON TABLE HOLD FORMAT HTML AS 'X1' ON TABLE SET PAGE-NUM OFF ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = overtime_blue.sty, TOPMARGIN=0.0, LEFTMARGIN=0.0, RIGHTMARGIN=0.0, BOTTOMMARGIN=0.0, $ TYPE=HEADING, STYLE=BOLD, $ TYPE=REPORT, BACKCOLOR=RGB(153 204 255), FONT=ARIAL, $ TYPE=REPORT COLUMN=3, WIDTH=1.2, WRAP=1.2, SIZE=9, $ END -HTMLFORM BEGIN <html> <body style="background-color:#99CCFF;")> <div valign="top" style="width:150px;"> !IBI.FIL.X1; </div> </body> </html> -HTMLFORM END
quote:ON TABLE HOLD FORMAT HTML AS 'X1'
quote:APPROVAL_DATE
quote:APPROVAL_DATE1/YYMD = HDATE
(APPROVAL_DATE,'YYMD');
quote:APPROVAL_DATE1/YYMD = HDATE
(APPROVAL_DATE,'YYMD');
-* File District_Org_Chart.fex -SET &ECHO=ALL; -* -*-DEFAULT &DIVISION = '1D' ; -* DEFAULT &SUB_ASG = 'SHIFT 1' ; ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR SQL SQLORA select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER, INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE, INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE, OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID, EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A' and ROWNUM <= 10; -*TABLE FILE SQLOUT -*PRINT * -*ON TABLE HOLD FORMAT ALPHA -*END TABLE ON TABLE HOLD AS OFFNSDAT END -*RUN -*EXIT -RUN -*?FF HOLD -*-EXIT TABLE FILE OFFNSDAT SUM INCIDENT_SUPPLEMENTS.APPROVAL_DATE INCIDENTS.INC_REPORT_NUMBER INCIDENT_SUPPLEMENTS.CREATOR_ID INCIDENT_TYPE_CODES.DESCRIPTION OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE INCIDENTS.INCIDENT_ID INCIDENTS.ITC_CODE OFFENSES.INC_INCIDENT_ID EMPLOYEES.LOGIN_ID EMP_SERVICE_HISTORIES.EJS_EMP_ID EMPLOYEES.EJS_EMP_ID EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE BY OFFICER_ID ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID -RUN -*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1 -*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1 -*-SET &MYSTAMP = &YYMD; JOIN OFFICER_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1 DEFINE FILE OFFNSDAT APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD'); YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ; LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ; FDATEYST/A18YYMD=YST ; FDATELST2/A18YYMD=LST2 ; END TABLE FILE OFFNSDAT HEADING CENTER "&SUB_ASG" PRINT UPLASTNAME AS '' APPROVAL_DATE1 BY RANK_ORDER NOPRINT BY RANK_ABRV AS '' WHERE TSDIVISION EQ '&DIVISION'; WHERE SUB_ASSIGNMENT EQ '&SUB_ASG'; WHERE RANK_ABRV NE 'CPT' OR 'LT'; //Updated to APPROVAL_DATE1 WHERE APPROVAL_DATE1 EQ FDATEYST; -*WHERE APPROVAL_DATE EQ FDATEYST; -*WHERE APPROVAL_DATE GE FDATELST2; -*ON TABLE SET EMPTYREPORT ON -*ON TABLE HOLD FORMAT HTML AS 'X1' ON TABLE HOLD FORMAT HTMTABLE AS 'X1' ON TABLE SET PAGE-NUM OFF ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = overtime_blue.sty, TOPMARGIN=0.0, LEFTMARGIN=0.0, RIGHTMARGIN=0.0, BOTTOMMARGIN=0.0,
-SET &HI_DATE = &YYMD | ' 00:00:00'; -TYPE &HI_DATE -SET &LO_DATE = DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'D',-2)),'YYMD','A8YYMD') | ' 00:00:00'; -TYPE &LO_DATE(only tested on SQL server, so hope Oracle is the same).
WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= &LO_DATE.QUOTEDSTRING AND THISDATE < &HI_DATE.QUOTEDSTRING
JOIN OFFICER_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTERAS J1