Loop with SQL Script

[neerajthakur]

Hi, 

I want to run this script with Month Value as Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

and Year from 2020,2021,2022 to whatever is current

I want to run below script with start date of each month from 2020 and store in format Table_Jan_2020.qvd till Current Month for example March 2023 date will be '2023-01-01' and Store will be Table_Mar_2023

select max(processed_on)
from fbo_application_process_log where ref_id=fad.ref_id
and processed_on>aa.max_date and to_char(processed_on,'yyyy-MM-dd')<='2020-01-01'

[Marijn]

Hi @neerajthakur
Have you tried creating a for-loop for the years + a nested for loop for the months?

FOR i = 2020 to year(today())
   FOR j = 1 to 12

   next j
next i

[Gabbar · Accepted solution]

Lets First Create a Calendar with Required Dates Like this:-

let vtoday = Floor(Num(monthend(addmonths(Today(),-1))+1));
let vpast = Floor(Num(monthend(yearstart(addmonths(addyears(Today(),-3),-1)))+3));

;

A:
Load distinct Monthstart(TempDate) as MonthStart;
LOAD
$(vpast) + Iterno()-1 As Num,
Date($(vpast) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vpast) + IterNo() -1 <= $(vtoday);

NoConcatenate
B:
Load Distinct Date(Date#(MonthStart,'DD/MM/YYYY'),'DD_MM_YYYY') as Month,RowNo() as sr
,Month(MonthStart-1) as Month_Name,Year(MonthStart-1) as Year_Name Resident A;

Drop Table A
;



After This Use a loop to fetch Data from the table and Use those Dates Like this:- 

LIB CONNECT TO 'Source';
For vFileNo = 1 to NoOfRows('B')
Let vFileName = Peek('Month',vFileNo-1,'B');
Trace $(vFileName);
Trace $(vFileNo);
NoConcatenate
$(vFileName):
select max(processed_on)
from fbo_application_process_log where ref_id=fad.ref_id
and processed_on>aa.max_date and to_char(processed_on,'dd_MM_yyyy)<='$(vFileName)'
;

Trace $(vFileName);
Next

Add the store and Drop function within the loop only

[neerajthakur]

@Gabbar Thanks, it helped a lot. Keep it up 👍