quote:

-* So here is my Date Range for the Week, Next Week

-* FR_DATE will look like '2011/05/08 00:00:00'
-SET &FR_DATE = SUBSTR(8, &FR_DATE, 1, 4, 4, 'A4') | '/' | SUBSTR(8, &FR_DATE, 5, 6, 2, 'A2') | '/' | SUBSTR(8, &FR_DATE, 7, 8, 2, 'A2') || ' 00:00:01';

-* TO_DATE will look like '2011/05/14 00:00:00'
-SET &TO_DATE = SUBSTR(8, &TO_DATE, 1, 4, 4, 'A4') | '/' | SUBSTR(8, &TO_DATE, 5, 6, 2, 'A2') | '/' | SUBSTR(8, &TO_DATE, 7, 8, 2, 'A2') || ' 23:59:59';


-* There is a field in my data call SCHEDULEDDATE formated as HYYMDs data looks like '2011/05/09 24:12:02'
-* I plan to build a new field with either the SCHEDULEDATE (because it falls within the week of data) or slam the turn of the century into the field
-* so that when I present the Week of data, there will be an extra column at the front. I'll label that with the header 'Not,Scheduled'

DEFINE FILE [TABLENAME]
NEW_SCHEDDATE/HYYMD = IF(SCHEDULEDATE GE DT('&FR_DATE') AND SCHEDULEDATE LE DT('&TO_DATE')) THEN DT('1900/01/01 00:00:00.000') ELSE SCHEDULEDATE;

DATE_FIX/I8YYMD=NEW_SCHEDDATE;
DYWEEK/A12=IF NEW_SCHEDDATEE EQ '1900/01/01 00:00:00' THEN 'Not,Sched' ELSE DOWK(DATE_FIX,DYWEEK);
END

TABLE FILE [TABLENAME]
PRINT *
ACROSS NEW_SCHEDDATE NOPRINT
ACROSS DYWEEK AS 'Scheduled Call Dates for Week Ending &WEEKEND.EVAL'
ACROSS NEW_SCHEDDATE AS ' '
END

quote:

0 NUMBER OF RECORDS IN TABLE= 758 LINES= 758
-******************************************************************************
-* Final data stage for the report *
-******************************************************************************
JOIN CLEAR *
JOIN
TERR_ID IN AGING04 TO TERRITORYID IN LKUPDSTREGN AS J1
END
JOIN
TERR_ID IN AGING04 TO TERR_ID IN HLDTERR AS J2
END
-RUN
DEFINE FILE AGING04
LastCall/MDYY = HDATE(DATEEND, 'MDYY');
NEW_SCHEDDATE/HYYMDs = IF(SCHEDULEDATE GE DT('2011/05/08 00:00:01.000') AND SCHEDULEDATE LE DT('2011/05/14 23:59:59.000')) THEN DT('1900/01/01 00:00:00.000') ELSE SCHEDULEDATE;
END
TABLE FILE AGING04
SUM
MAX.ACCOUNTNAME
MAX.SCHEDULEDATE
MAX.DATEEND
MAX.LastCall
MAX.ADDRESS4
MAX.STATE
COMPUTE NumDays/D6 = HDIFF(MAX.DATEEND, MAX.SCHEDULEDATE, 'DAYS', 'D6');
COMPUTE CallOffset/D6 = IF MAX.NumDays LE 0 THEN (MAX.CALLFREQUENCY + MAX.NumDays) ELSE (MAX.CALLFREQUENCY - MAX.NumDays);
NEW_SCHEDDATE AS 'SCHEDULEDATE'
MAX.PRIMARYGROUP_ID
MAX.PRIMARYGROUPNAME
MAX.TERR_ID
MAX.CALLFREQUENCY
BY REGIONID
BY DISTRICTID
BY REPNAME
BY ACCOUNT_ID
ON TABLE HOLD AS HLDCAL
END
-RUN
0 ERROR AT OR NEAR LINE 521 IN PROCEDURE call_plan_summary_
(FOC177) INVALID DATE CONSTANT: '2011/05/08 00:00:01.000'
0 ERROR AT OR NEAR LINE 534 IN PROCEDURE call_plan_summary_
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: NEW_SCHEDDATE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

quote:

NEW_SCHEDDATE/HYYMD = IF(SCHEDULEDATE GE '&FDATETM') AND SCHEDULEDATE LE '&TDATETM')) THEN DT('1900/01/01 00:00:00') ELSE SCHEDULEDATE;

  
-SET &FR_DATE = '20110508';
-SET &FR_DATE = EDIT('&FR_DATE.EVAL','9999/99/99');
-SET &TO_DATE = '20110514';
-SET &TO_DATE = EDIT('&TO_DATE.EVAL','9999/99/99');
DEFINE FILE [TABLENAME]
  SCHED_DATE/YYMD    = HDATE(SCHEDULEDATE,'YYMD');
  HARD_CD_DT1/I8YYMD = 19000101;
  HARD_CD_DT2/YYMD   = HARD_CODE_DT1;
  NEW_SCHEDDATE/YYMD = IF ((SCHED_DATE GE '&FR_DATE.EVAL') AND (SCHED_DATE LE '&TO_DATE.EVAL')) THEN HARD_CODE_DT2 ELSE SCHED_DATE; 
  DATE_FIX/I8YYMD    = NEW_SCHEDDATE;
  DYWEEK/A10         = IF NEW_SCHEDDATE EQ HARD_CD_DT2 THEN 'Not,Sched' ELSE DOWK(DATE_FIX,DYWEEK);
END

WHERE START_DT_CORRECTED FROM DT(20011101 00:00:00.000) TO DT(20021031 23:59:59.999);
...
WHERE CREATEDON GE DT(2006-07-01 00:00:00.000)
...
COMPUTE ERNG_DT_CORRECTED/HYYMDS = HADD(DT(&ERNG.EVAL 23:59:59.999), 'HOUR', -(&CORR_HOURS), 8, 'HYYMDS');
...
DEFINE FILE XXX
FUTUREDATE/HYYMDS = DT(&FUTUREDATE 00:00:00);
DUMMYDATE/HYYMDS = DT(00000000 00:00:00);
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server