Hi! I'm trying to loop through 12 months (starting 12 months ago), setting each month to display the end of the month. With each iteration, I'd like to calculate the monthly backlog by determining whether the created date occurred before the end of the month and the closed date either hasn't occurred yet or occurred after the end of the month. Does anyone know how to do this without using Dialog Manager commands? I can accomplish this with Dialog Manager commands but I was told that unless I'm using a SQL pass through, all of the date manipulations should be done using DEFINE's. Thanks for any help you can provide!

This message has been edited. Last edited by: Kerry,

You have to use DEFINE's when the values are tied to your data fields. In your case, it appears they are not. You just want the last 12 end-of-month dates from today (as determined by &YYMD or something similar.)

You can still calculate the end of month date for the last 12 months using dialogue manager and then compare your fields' values to those & vars.

So calculate in a loop the twelve dates, like &MTH1, &MTH2, etc.. Then use a define like

MONTH1_BKLOG/I9=IF CREATE_DATE LE &MTH1 AND (CLOSE_DATE GT &MTH1 OR CLOSE_DATE EQ MISSING) THEN 1 ELSE 0;
MONTH2_BKLOG/I9=IF CREATE_DATE LE &MTH2 AND (CLOSE_DATE GT &MTH2 OR CLOSE_DATE EQ MISSING) THEN 1 ELSE 0;

DM is the most powerful/effecient way to control execution flow in a FOCUS pgm, I would first sincerely suggest reviewing that "do-not-use" rule in the first place.

You can do the date manipulation inside a DEFINE statement by assigining Month & Last-day values in each iteration of a -REPEAT loop.

One suggestion -

  
-REPEAT RPT_LOOP FOR &I FROM 1 TO 12;
DEFINE CLEAR *
DEFINE FILE xxxx
YR/A4='2009';
MTH/A2= IF &I < 10 THEN ('0' || EDIT(&I))
        ELSE EDIT(&I) ;
DY/A2=DECODE &I(1 31 2 28 3 31 .....12 31);
DTE/A10= YR || '-' || MTH || '-' || DY ;
END
TABLE FILE xxx
PRINT xxxxxx
WHERE XXX_DT1 < DTE
END
-RPT_LOOP

you can't put an entire define within a repeat loop - each time the loop is executed the previous define will be erased. But the idea is feasible, although there's a much easier way to calculate your end-of month dates using DATEADD and DATEMOV.

You can do it in the following manner also -- this uses older subroutines. You could place it in a loop and index your &vars with .&ctr. Although the new functions are much easier to code.

 
-SET &REMAINDER = IMOD(&CURYR, 4, 'I3L');
-SET &EOM = IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER EQ 0) THEN '29' ELSE
- IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER NE 0) THEN '28' ELSE
- IF ('&CLOSEMM.EVAL' EQ '01' OR '03' OR '05' OR '07' OR '08' OR '10' OR '12') THEN '31' ELSE '30';

Thanks to you all! I ended up using Darin's first suggestion. I appreciate the help!

Linus, I liked your suggestion, but if I can code less and get the same result - I choose that, so my take on your code would be the same except the last line would be:

quote:

-SET &REMAINDER = IMOD(&CURYR, 4, 'I3L');
-SET &EOM = IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER EQ 0) THEN '29' ELSE
- IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER NE 0) THEN '28' ELSE
- IF ('&CLOSEMM.EVAL' EQ '04' OR '06' OR '09') THEN '30' ELSE '31';

Ira

This message has been edited. Last edited by: ira,

Ira,

So, November has 31 days?

Also, I am a little surprised EOM function has not been suggested:

  
-SET &CUR_DT0  = DATECVT(DATEADD(DATECVT (&YYMD,   'I8YYMD', 'YYMD'),'M', -5 ), 'YYMD','I8YYMD');
-SET &EOM_DT0  = DATECVT(DATEMOV(DATECVT(&CUR_DT0, 'I8YYMD', 'YYMD'),   'EOM'),'YYMD', 'I8YYMD');

-SET &CUR_DT1  = DATECVT(DATEADD(DATECVT (20090220,'I8YYMD', 'YYMD'),'M', -12), 'YYMD','I8YYMD');
-SET &EOM_DT1  = DATECVT(DATEMOV(DATECVT(&CUR_DT1, 'I8YYMD', 'YYMD'),   'EOM'),'YYMD', 'I8YYMD');

-SET &CUR_DT2  = DATECVT(DATEADD(DATECVT (20080220,'I8YYMD', 'YYMD'),'M', -12), 'YYMD','I8YYMD');
-SET &EOM_DT2  = DATECVT(DATEMOV(DATECVT(&CUR_DT2, 'I8YYMD', 'YYMD'),   'EOM'),'YYMD', 'I8YYMD');

-SET &CUR_DT3  = DATECVT(DATEADD(DATECVT (20070220,'I8YYMD', 'YYMD'),'M', -12), 'YYMD','I8YYMD');
-SET &EOM_DT3  = DATECVT(DATEMOV(DATECVT(&CUR_DT3, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');

-TYPE EOM_DT0: &EOM_DT0
-TYPE EOM_DT1: &EOM_DT1
-TYPE EOM_DT2: &EOM_DT2
-TYPE EOM_DT3: &EOM_DT3
-EXIT

Output:

 EOM_DT0: 20090228
 EOM_DT1: 20080229
 EOM_DT2: 20070228
 EOM_DT3: 20060228

quote:

You can still calculate the end of month date for the last 12 months using dialogue manager and then compare your fields' values to those & vars.

So calculate in a loop the twelve dates, like &MTH1, &MTH2, etc..

That was what I was suggesting without giving too much away. Subtract a month from today's date and then move to end of month (times 12).