8 Replies Latest reply: Mar 1, 2017 8:30 PM by m m

# For Loop

I need to loop 3 times thru each month. days 01-10, 11-20, 21-31

//Starting Variable to pass YYMM

vStartYYMM='201702'

Loop1

vStartDate=\$(vStartYYMM)01

vStartDate=\$(vStartYYMM)10

Loop2

vStartDate=\$(vStartYYMM)11

vStartDate=\$(vStartYYMM)20

Loop3

vStartDate=\$(vStartYYMM)21

vStartDate=\$(vStartYYMM)31

How do you create the days portion via loop.

• ###### Re: For Loop

Why do you need 3 loop?

LET vStartYYMM='201702';

FOR i=1 to 10

LET vStartDate=\$(vStartYYMM)& num(\$(i), '00');

TRACE \$(vStartDate);

NEXT

FOR i=1 to 10

LET vStartDate=\$(vStartYYMM)& num(10 + \$(i), '00');

TRACE \$(vStartDate);

NEXT

FOR i=1 to 11

LET vStartDate=\$(vStartYYMM)& num(20 + \$(i), '00');

TRACE \$(vStartDate);

NEXT

• ###### Re: For Loop

Because i am doing data pull by increments.

for u = 1 to 31 step 11

Let vStartu= \$(vmonth0)&num(\$(u),'00');

Let vEndu= \$(vmonth0)&num(9+\$(u),'00');

trace 'vStartu = '\$(vStartu);

trace 'vEndu = ' \$(vEndu);

next

so above works to a certain degree but not exactly right.

OUTPUT

'vStartu = '20170201

'vEndu = ' 20170211

'vStartu = '20170212

'vEndu = ' 20170222

'vStartu = '20170223

'vEndu = ' 20170233

Last vEndu date is exceeding 31 days.

• ###### Re: For Loop

Will this work for You??

LET vStart=NUM(MonthStart(TODAY()));

TEMP:

\$(vStart)+ROWNO()-1 AS NumDate,

Date(\$(vStart)+ROWNO()-1,'DDMMYYYY') as Date

AutoGenerate

Num#(Date(MonthEnd(TODAY()),'DD'));

DateLoop:

If(Left(Date,2)<='10',Makedate(Year(Date),Month(Date),10),

If(Left(Date,2)<='20' and Left(Date,2)>'10',Makedate(Year(Date),Month(Date),20),

If( Left(Date,2)>'20',Makedate(Year(Date),Month(Date),num#(Date(MonthEnd(Date),'DD')))

)

)

) as EndDate,

If(Left(Date,2)<='10',Makedate(Year(Date),Month(Date),1),

If(Left(Date,2)<='20' and Left(Date,2)>'10',Makedate(Year(Date),Month(Date),11),

If( Left(Date,2)>'20',Makedate(Year(Date),Month(Date),21)

)

)

) as StartDate

Resident TEMP;

Drop table TEMP;

• ###### Re: For Loop

LET vMonthStart = MonthStart(Date#('\$(vStartYYMM)', 'YYYYMM'));

LET vMonthEnd = MonthEnd(Date#('\$(vStartYYMM)', 'YYYYMM'));

TRACE \$(vMonthStart);

FOR i=0 TO 2

LET vStartDate = Date('\$(vMonthStart)' + 10 * i, 'YYYYMMDD');

TRACE \$(vStartDate);

IF (i=2) THEN

LET vEndDate = Date('\$(vMonthEnd)', 'YYYYMMDD');

ELSE

LET vEndDate = Date('\$(vMonthStart)' + 10 * i + 9, 'YYYYMMDD');

ENDIF

TRACE \$(vEndDate);

NEXT

• ###### Re: For Loop

Thank you.

• ###### Re: For Loop

so if you add 10 days to a date and then multiple by 0, you get the same date you started with?. It won't result in NULL?

2017/02/01 + 10 = 2017/02/11 * 0 = 2017/02/01  ?

• ###### Re: For Loop

multiply is before sum

2017/02/01 + 10 * 0 = 2017/02/01 + 0 = 2017/02/01

2017/02/01 + 10 * 1 = 2017/02/01 + 10 = 2017/02/11

• ###### Re: For Loop

Thank you.