topic Re: Load current month data with do while in Brasil

Load current month data with do while

MarioCenteno — Fri, 27 Jul 2018 06:29:48 GMT

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;

YoussefBelloum — Fri, 27 Jul 2018 06:54:42 GMT

Hi,

what are you trying to do exactly here ? filtering your load on the current month using a where ?

what is the format of your field Date ?

MarioCenteno — Fri, 27 Jul 2018 06:59:40 GMT

Attempt to bring sales for the current month, the date format is dd / mm / yyyy

If I change the value in the LET variable vI = 1; // INITIAL MONTH shows me data from January to June but excludes me from the current month

I need you to upload data until the current month from January to July for example.

YoussefBelloum — Fri, 27 Jul 2018 07:02:53 GMT

if you remove the where clause, what do you get ?

MarioCenteno — Fri, 27 Jul 2018 07:08:49 GMT

If I delete the date clause where there is no data. And the consultation takes too long.

YoussefBelloum — Fri, 27 Jul 2018 07:14:04 GMT

remove the loop and replace your where clause with this:

where DATE >= Date(YearStart(Today()),'DD/MM/YYYY') and DATE <= Date(MonthEnd(Today()),'DD/MM/YYYY')

this will load data from 01/01/2018 until the end of the actual month

MarioCenteno — Fri, 27 Jul 2018 07:21:51 GMT

I generated error I used the correct variables of the table

WHERE b.date_process >= b.date_process (YearStart(Today()),'DD/MM/YYYY') and b.date_process <= b.date_process (MonthEnd(Today()),'DD/MM/YYYY'

When i use clause WHERE b.fecha_proceso = trunc(LAST_DAY('$(vCiclo)')) It shows me the data from January to June

YoussefBelloum — Fri, 27 Jul 2018 07:53:55 GMT

what is the dimension used on your filterbox ? is it DATE ?