Hi all,
I am trying to generate a loop in order to create months between the start and end date of a contract.
Is it possible to create them directly at month level?
This is my script,
SAHEAD_Periodos:
LOAD
CACTNO as MTO_ContratoNum,
makedate(left(CAVADF,4),mid(CAVADF,5,2),right(CAVADF,2)) AS MTO_InicioContrato,
makedate(left(CAVADT,4),mid(CAVADT,5,2),right(CAVADT,2)) AS MTO_FinContrato
FROM $(vPath)SAHEAD.qvd (qvd) WHERE left(CAAGTP,1) <> 'M' AND (CAVADT>=20150101 OR CAVADF>=20150101);
SAHEAD_Periodos_Loop:
LOAD MTO_ContratoNum, MTO_InicioContrato, MTO_FinContrato, ROWNO() AS MTO_NumeroLinea,
makedate(year(Date(MTO_InicioContrato+iterno()-1)),month(Date(MTO_InicioContrato+iterno()-1))) as MTO_MesVigente
resident SAHEAD_Periodos while MTO_InicioContrato+IterNo()-1 <=MTO_FinContrato;
DROP TABLE SAHEAD_Periodos;
SAHEAD_Ordenado:
LOAD *,
If(previous(MTO_ContratoNum)=MTO_ContratoNum AND previous (MTO_MesVigente)=MTO_MesVigente,0,1) AS Flag_Repetidos
;
LOAD distinct MTO_ContratoNum, MTO_InicioContrato, MTO_FinContrato, MTO_MesVigente, 1 as test
resident SAHEAD_Periodos_Loop order by MTO_ContratoNum,MTO_MesVigente;
DROP TABLE SAHEAD_Periodos_Loop;
DROP FIELD test;
SAHEAD:
LOAD *, 1 as TestLineas
RESIDENT SAHEAD_Ordenado WHERE Flag_Repetidos = 1 AND MTO_MesVigente >=42005 AND MTO_MesVigente <= today();
DROP TABLE SAHEAD_Ordenado;
DROP FIELD TestLineas;
Thanks in advance,
Ana
