Date Calculation

[microwin88x]

Hello,

I have the following table with fields:

• ID: ID from Contract
• POS: Position Number
• START_DATE: Date Format DD/MM/YYYY
• END_DATE: Date Format DD/MM/YYYY
• DAYS: Num of Days to Sum Contract

ID	ID_POS	START_DATE	END_DATE	DAYS
COD00990	0	24/2/2012	23/2/2015	18
COD00990	1	24/2/2012	23/2/2015	18
COD00990	2	24/2/2012	23/2/2015	12
COD00991	0	23/5/2011	22/5/2015	12
COD00991	1	23/5/2011	22/5/2015	12
COD00991	2	23/5/2011	22/5/2015	12
COD00991	3	23/5/2011	22/5/2015	12
COD00992	0	17/3/2015	15/5/2015	18
COD00992	1	17/3/2015	15/5/2015	18
COD00993	0	23/3/2017	22/6/2018	12

What I need to get is something like this by script:

• EXTENSION_START_DATE: Date Format DD/MM/YYYY
• EXTENSION_END_DATE: Num of Days to Sum Contract

ID	ID_POS	START_DATE	END_DATE	DAYS	EXTENSION_START_DATE	EXTENSION_END_DATE
COD00990	0	24/2/2012	23/2/2015	18	24/2/2015	14/3/2015
COD00990	1	24/2/2012	23/2/2015	18	15/3/2015	2/4/2015
COD00990	2	24/2/2012	23/2/2015	12	3/4/2015	15/4/2015
COD00991	0	23/5/2011	22/5/2015	12	23/5/2015	4/6/2015
COD00991	1	23/5/2011	22/5/2015	12	5/6/2015	17/6/2015
COD00991	2	23/5/2011	22/5/2015	12	18/6/2015	30/6/2015
COD00991	3	23/5/2011	22/5/2015	12	1/7/2015	13/7/2015
COD00992	0	17/3/2015	15/5/2015	18	16/5/2015	3/6/2015
COD00992	1	17/3/2015	15/5/2015	18	4/6/2015	22/6/2015
COD00993	0	23/3/2017	22/6/2018	12	23/6/2018	5/7/2018

To calculate the EXTENSION_START_DATE:

If ID_POS = 0, then EXTENSION_START_DATE = END_DATE + 1

If ID_POS > 0, the EXTENSION_START_DATE = PREVIOUS (EXTENSION_END_DATE) + 1

To calculate the EXTENSION_END_DATE:

EXTENSION_END_DATE = EXTENSION_START_DATE + DAYS

Is there any way to do this? I attach a sample QVW file and it's Excel with the data.

Thank you!!!

[vamsee]

I don't know if I understood your question right, but you have posted the answer urself.

Anyway,

Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0,PREVIOUS (EXTENSION_END_DATE) + 1))) as EXTENSION_START_DATE,
Date(EXTENSION_START_DATE + DAYS) as EXTENSION_END_DATE

[microwin88x]

Hello Vamsee,

The problem is that I don't have the fields EXTENSION_START_DATE and EXTENSION_END_DATE on the first place, I just have the following:

ID	ID_POS	START_DATE	END_DATE	DAYS
COD00990	0	24/2/2012	23/2/2015	18
COD00990	1	24/2/2012	23/2/2015	18
COD00990	2	24/2/2012	23/2/2015	12
COD00991	0	23/5/2011	22/5/2015	12
COD00991	1	23/5/2011	22/5/2015	12
COD00991	2	23/5/2011	22/5/2015	12
COD00991	3	23/5/2011	22/5/2015	12
COD00992	0	17/3/2015	15/5/2015	18
COD00992	1	17/3/2015	15/5/2015	18
COD00993	0	23/3/2017	22/6/2018	12

So when I use this it doesn't find those fields:

Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0,PREVIOUS (EXTENSION_END_DATE) + 1))) asEXTENSION_START_DATE,
Date(EXTENSION_START_DATE + DAYS) as EXTENSION_END_DATE

[vamsee]

Yeah the above code still works.

[microwin88x]

It works because you're using the Extension_Start_Date and Extension_End_Date from the Excel file, but you shouldn't because we need to generate those fields using the original data we have, which is just this:

ID	ID_POS	START_DATE	END_DATE	DAYS
COD00990	0	24/2/2012	23/2/2015	18
COD00990	1	24/2/2012	23/2/2015	18
COD00990	2	24/2/2012	23/2/2015	12
COD00991	0	23/5/2011	22/5/2015	12
COD00991	1	23/5/2011	22/5/2015	12
COD00991	2	23/5/2011	22/5/2015	12
COD00991	3	23/5/2011	22/5/2015	12
COD00992	0	17/3/2015	15/5/2015	18
COD00992	1	17/3/2015	15/5/2015	18
COD00993	0	23/3/2017	22/6/2018	12

[vamsee · Accepted solution]

My Bad. I didn't realize that. Try

DATA:
Load 
*,
Date(EXTENSION_START_DATE + DAYS)  as EXTENSION_END_DATE
;
Load 
*,
Date(IF(ID_POS=0,END_DATE + 1, IF(ID_POS>0, Peek(EXTENSION_START_DATE) + DAYS  + 1))) as EXTENSION_START_DATE
;
LOAD
ID,
ID_POS,
START_DATE,
END_DATE,
DAYS
FROM BD_Example.xlsx (ooxml, embedded labels, table is Example);

[microwin88x]

Thank you! It works now!