how to generate month, quarter and year between two dates

[hariprasad9]

Hi All,

i have two dates ( from_date & to_date) and i would like to get all the quarters/month/years between those given from and to dates. Can someone please help URGENT!!

Example of data - from_date = 10-JAN-2018 and to_date10-feb-2019

then quarters should be Calendar - 2018Q1,Q2,Q3,Q4 , 2019Q1 and Fiscal 2018FQ2,FQ3,FQ4,2020FQ1,FQ2

[tincholiver]

Try with this:
Load the table that you have both dates:
take the min and max dates:
// Paso 1:
TablaMinMax:
LOAD
min(Fecha) as FechaMin,
max(Fecha) as FechaMax
Resident Payments;

//Paso 2:
LET vMin=num(Peek('FechaMin',0,'TablaMinMax'));
LET vMax=num(Peek('FechaMax',0,'TablaMinMax'));

// Paso 3:
CalendarioMaestro:
LOAD
Date(IterNo()+$(vMin)-1) as Fecha
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

// Paso 4:
DROP Table TablaMinMax;

Calendario:
LOAD
Date(Fecha) as Fecha,
Year(Fecha)&Num(Month(Fecha),'00') as Periodo,
Year(Fecha) as Año,
Year(Fecha)&'Q'&Ceil (month(Fecha)/3) as Cuatrimestre,
Month(Fecha) as Mes,
Num(Month(Fecha)) as MesNro,
Num(Month(Fecha)-1) as MesNroAnt,
Year(Fecha)*12+Num(Month(Fecha)) as MesAnt,
MonthName(Fecha) as MesAño,
Num(Month(Fecha),'00') as MesNumero,
'T-'&Ceil (month(Fecha)/4) as Trimestre,
Day(Fecha) as Dia,
Dual(Num(Day(Fecha),'00')& '-'&Month(Fecha),Fecha) as Dia_Mes,
WeekDay(Fecha) as DiaSemana;

LOAD Date(IterNo()+$(vMin)-1) as Fecha
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

DROP Table CalendarioMaestro;


Let me know if was usefull for you.

[tincholiver]

Add this line to Fiscal date:

Year(Fecha)&'F'&'Q'&Ceil (month(Fecha)/3) as Fiscal,

[tincholiver]

Sorry, there is an error in the previous script. try with this:

// Optional: if you want to manually load the dates you can do it here:
FactTable:
LOAD * INLINE [
Fecha,
01/01/2015
31/12/2020];

// Another option is to take the dates from the table that you are loading
FactTable:
LOAD * FROM Table;

// Paso 1:
TablaMinMax:
LOAD
min(Fecha) as FechaMin,
max(Fecha) as FechaMax
Resident FactTable;

//Paso 2:
LET vMin=num(Peek('FechaMin',0,'TablaMinMax'));
LET vMax=num(Peek('FechaMax',0,'TablaMinMax'));

// Paso 3:
CalendarioMaestro:
LOAD
Date(IterNo()+$(vMin)-1) as Fecha
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

// Paso 4:
DROP Table TablaMinMax;

Calendario:
LOAD
Date(Fecha) as Fecha,
Year(Fecha)&Num(Month(Fecha),'00') as Periodo,
Year(Fecha) as Año,
Year(Fecha)&'Q'&Ceil (month(Fecha)/3) as Cuatrimestre,
Year(Fecha)&'F'&'Q'&Ceil (month(Fecha)/3) as Fiscal,
Month(Fecha) as Mes,
Num(Month(Fecha)) as MesNro,
MonthName(Fecha) as MesAño,
'T-'&Ceil (month(Fecha)/4) as Trimestre,
Day(Fecha) as Dia,
Dual(Num(Day(Fecha),'00')& '-'&Month(Fecha),Fecha) as Dia_Mes,
WeekDay(Fecha) as DiaSemana;

LOAD Date(IterNo()+$(vMin)-1) as Fecha
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

DROP Table FactTable;