Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
Will this work for You??
LET vStart=NUM(MonthStart(TODAY()));
TEMP:
LOAD
$(vStart)+ROWNO()-1 AS NumDate,
Date($(vStart)+ROWNO()-1,'DDMMYYYY') as Date
AutoGenerate
Num#(Date(MonthEnd(TODAY()),'DD'));
DateLoop:
Load Distinct
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;
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
Thank you.
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 ?
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
Thank you.