Load current month data with do while

[MarioCenteno]

I am using a do while to try to bring data for the current month, but only upload data until the previous month I need your help please.

/* >> LOADA DATA PER PERIODS ========================================= */

LET vPeriodo = (YEAR(TODAY())) & '0'; // CURRENT A YEAR

LET vMesActual = NUM(MONTH(TODAY())); // CURRENT ACTUAL

LET vMesProximo = NUM(MONTH(TODAY())+1); // NEXT MONTH

LET vI = 7; // INITIAL MONTH

DO WHILE vI <= vMesActual

LET vCiclo = vPeriodo & vI; // BUILD YEAR-MONTH

TRACE $(vCiclo);

LET vCiclo = vCiclo & '01'; // BUILD YEAR-MONTH-FIRSTDAY

LOAD

SELECT * FROM TABLE

WHERE DATE =  TRUNC(LAST_DAY('$(vCiclo)'))

LET vI = vI + 1;

LOOP;

[sunilkumarqv]

but only upload data until the previous month I need your help please.

if DATE = 2018-08-27

Let  vPrevmonth= Year(today())&'-'&Month(AddMonths(Today(),-1))&'-'&Day(today())

LOAD

SELECT * FROM TABLE

WHERE DATE <=$(vPrevmonth)

[DavidŠtorek]

Hi,

is there any special reason why you need to use loop?

I don't know your date format but if you want to load all data excluding current month I would do it as following

Let vMaxDate= MonthStart(Today());

Select * ...

Where Date < Date '$(vMaxDate)';

[MarioCenteno]

Maybe I interpreted badly, I need the data until the current month July, with the script that I have only shows me until June.

[DavidŠtorek]

And you have data for longer period than until now?

Let vMaxDate= Year(Today())&If(Num(Month(Today()))>9,Num(Month(Today())),0&Num(Month(Today())));

Select * ...

Where Date <= '$(vMaxDate)';

[petter]

The reason why you only get data until June is that the date you are filtering on is the last day of the month of each month. So for July it will be the 31st of July 2018 which hasn't arrived yet. So it will only be on the actual 31st of July 2018 that the July numbers will be available for you... unless of course there are some future values existing which I suspect is not the case....

Be aware that your code also has some logical flaws that will make it fail when you get past September since you are prepending the month number with a 0 which you shouldn't do unconditionally when you get into the last four months of the year.

[petter]

Referring to my previous response here is your script modified so it caters for the logical error of always prepending month numbers with a zero:

/* >> LOADA DATA PER PERIODS ========================================= */
//LET vPeriodo = (YEAR(TODAY())) & '0'; // CURRENT A YEAR
  LET vPeriodo = (YEAR(TODAY())); // CURRENT A YEAR
LET vMesActual = NUM(MONTH(TODAY())); // CURRENT ACTUAL
LET vMesProximo = NUM(MONTH(TODAY())+1); // NEXT MONTH
LET vI = 7; // INITIAL MONTH

DO WHILE vI <= vMesActual
//  LET vCiclo = vPeriodo & vI; // BUILD YEAR-MONTH
  LET vCiclo = vPeriodo & Num(vI,'00'); // BUILD YEAR-MONTH
  TRACE $(vCiclo);
  LET vCiclo = vCiclo & '01'; // BUILD YEAR-MONTH-FIRSTDAY

  LOAD
  SELECT * FROM TABLE
  WHERE DATE BETWEEN TO_DATE('$(vCiclo)','YYYYMMDD') AND TRUNC(LAST_DAY('$(vCiclo)'));
  LET vI = vI + 1;
LOOP;

[MarioCenteno]

This is what I wanted in my code, now it works for me and I create data for July.

Is there a way for the data to be processed for each day of the month and arrive at the real day?

I would like to be loading the data for example July 1, July 2, July 3 .... July 31 and avoid loading the month at once.

Thank you all for helping me in these moments.

[petter]

Sure

vDate = Date('20180701','YYYYMMDD'); 
DO WHILE vDate <= Today(1)
  SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD'); 
  LET vDate = Date( vDate+1 ,'YYYYMMDD');
LOOP

The start day can span over years and months too since adding one for each new day will always calculate to the correct following day...

[MarioCenteno]

Now it does not show me the data, it loads other tables but the one that has the while is not loading anything.

I send the capture of the variables that we are using but the variable vDate shows me a weird data