Incremental load mensual with a do while

[MarioCenteno]

Hello I am trying to load data with a do while I need the load to be monthly, I currently do it day by day.

Current Script per day

vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD'); 

DO WHILE vDate <= Today(1) 

SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD'); 

  LET vDate = Date(Date#('$(vDate)', 'YYYYMMDD')+1 ,'YYYYMMDD'); 

LOOP

[eliran]

Hi Mario,

A simple tweak in your script will take care of this.

Explanation, Take the initial date, advance it in a full month.

I added the RangeMin formula to handle situations where you advance your date in a whole month, but because we're in the middle of the month, the date calculated will be a future date, thus not reloading the current month.

The rangemin fixes that.

  LET vDate = RangeMin(AddMonths('$(vDate)',1) ,Today()-1);

I hope it helps,

Eliran.

[jaimeaguilar]

Hi,

you can use a QMC task in order to extract data automatically monthly. Also in the script, just to be sure that data doesn't get extracted in other dates you can do something like this:

LET vDate = date(today(), 'YYYYMMDD')

IF $(vDate) = date(monthstart(today())) //LET'S SUPPOSE YOU WANT TO EXTRACT DATA EVERY FIRST DAY OF EVERY MONTH

THEN

SQL

--YOUR_SQL_SCRIPT

SELECT * FROM TABLE WHERE DATE = '$(vDate)';

ELSE

EXIT SCRIPT;  //WHEN THE CONDITION IS NOT FULFILLED THEN SCRIPT WILL STOP

ENDIF

this way, even if you or someone else runs the QMC task manually on a different date than it is scheduled, the script won't extract data,

hope this helps,

regards

[MarioCenteno]

Not working

[MarioCenteno]

I would like to define a departure date for example 20170101.

[MarioCenteno]

stalwar1‌ can u help me?

[Anonymous]

When you do the reload, do you keep the prior data?
If so, could you do a peek at the max date and then pull the data from that variable on?
That way it wouldnt matter the schedule (daily, weekly, monthly, etc.)

[balabhaskarqlik]

In var, you can use as:

MonthStart(Date)

vDate = Date(Date#(Monthstart(Date),'YYYYMMDD'),'YYYYMMDD'); 

[balabhaskarqlik]

May be check this:

Fiscal Calendar with Non-Standard Days (Not 1-31)