Need help in filling missing year with records

[karthikeyan1504]

jagan‌, sunindia‌, tresesco‌, swuehl‌, MRKachhiaIMP‌

Hi all,

I have staff table as shown below,

Staff No	Name	Department	Date Joined	Contract Expiry Date
19710	Mark	Insurance	30/4/2013	30/6/2015
30665	Ross	Retail	6/6/2012	30/6/2014
36698	Mike	Marketing	23/5/2014	30/6/2016

Based on the Date Joined and Contract expiry date, I need add the years between them. In short I need one more column with all the years from joined and expiry date as shown below,

Staff No	Name	Department	Date Joined	Contract Expiry Date	Year
19710	Mark	Insurance	30/4/2013	30/6/2015	2013/2014
19710	Mark	Insurance	30/4/2013	30/6/2015	2014/2015
30665	Ross	Retail	6/6/2012	30/6/2013	2012/2013
30665	Ross	Retail	6/6/2012	30/6/2013	2013/2014
30665	Ross	Retail	6/6/2012	30/6/2013	2014/2015
36698	Mike	Marketing	23/5/2014	30/6/2016	2014/2015
36698	Mike	Marketing	23/5/2014	30/6/2016	2015/2016

Could anyone please help me on this?

Thanks & Regards,

Karthikeyan.

[senpradip007]

Have a look at this.

[karthikeyan1504]

Thanks for your response. But I am getting only start year and end year from your solurtion. I need to have all the years in between joined and expiry date.

[t_chetirbok · Accepted solution]

Hi!

here just a little modify file

[tamilarasu]

Hi,

Try,

T1:
Load *, Year([Date Joined]) as JoinedYear, Year( [Contract Expiry Date]) as ExpiryYear;
LOAD * INLINE [
Staff No, Name, Department, Date Joined, Contract Expiry Date
    19710, Mark, Insurance, 30/4/2013, 30/6/2015
    30665, Ross, Retail, 6/6/2012, 30/6/2015
    36698, Mike, Marketing, 23/5/2014, 30/6/2016
];

T2:
load *, (JoinedYear + IterNo() -1) & '/' & (JoinedYear + IterNo()) as Year
Resident T1
While JoinedYear + IterNo() - 1 < ExpiryYear;

DROP Table T1;

[settu_periasamy]

Hi,

Try this..

SET DateFormat='D/M/YYYY';

Temp:
LOAD 2011+IterNo()-1 as Year AutoGenerate 1 While IterNo()<=10;

T1:
LOAD *,Year([Date Joined]) as doj_year,Year([Contract Expiry Date]) as ced_year;
LOAD * INLINE [
Staff No, Name, Department, Date Joined, Contract Expiry Date
19710, Mark, Insurance, 30/4/2013, 30/6/2015
30665, Ross, Retail, 6/6/2012, 30/6/2015
36698, Mike, Marketing, 23/5/2014, 30/6/2016
];

Inner Join(Temp)

IntervalMatch (Year) LOAD doj_year,ced_year Resident T1;

Left Join (T1)
LOAD * Resident Temp;

DROP Table Temp;

NoConcatenate
Final:
LOAD [Staff No],
Name, Department,
[Date Joined],
[Contract Expiry Date],Previous(Year)&' / '&Year as New
Resident T1 where AutoNumber(RecNo(),[Staff No])<>1 Order by [Staff No],[Date Joined] ;

DROP Table T1;