Start and end dates in selected periods

[chrisk44]

Hello,

I've got this source table:

EMPL_NO	DATE_FROM	DATE_TO	EMPL_DATE	CHANGE_DATE	PERIOD
4489	2006-10-11	2006-11-10	2006-10-11	2006-10-11	177758
4489	2006-10-11	2006-12-31	2006-10-11	2006-11-11	177758
4489	2007-01-01	2007-03-31	2007-01-01	2007-01-01	178319
4489	2007-04-01	2007-09-30	2007-04-01	2007-04-01	179654
4489	2007-10-01	2008-04-30	2007-10-01	2007-10-01	204028
4489	2007-10-01	2009-12-31	2007-10-01	2008-05-01	204028
4489	2007-10-01	2010-09-30	2007-10-01	2010-01-01	204028
4489	2007-10-01	2015-03-31	2007-10-01	2010-10-01	204028
4489	2007-10-01	2016-04-30	2007-10-01	2015-04-01	204028
4489	2007-10-01	_________	2007-10-01	2016-05-01	204028

Need to select first date_from in each period and last date_to in each period

Result table should be like that:

EMPL_NO	DATE_FROM	DATE_TO	PERIOD
4489	2006-10-11	2006-12-31	177758
4489	2007-01-01	2007-03-31	178319
4489	2007-04-01	2007-09-30	179654
4489	2007-10-01		204028

I'm new to QV so I ask for your understanding

Regards

[chrisk44 · Accepted solution]

Found a solution in another Thread:

[tmp]:

load
EMPL_NO,
DATE_FROM,
if (isnull(DATE_TO),Today()+100,DATE_TO) as DATE_TO,
PERIOD;
SELECT
FROM 

NoConcatenate
tmp2:
LOAD
EMPL_NO,
PERIOD,
date(MIN(DATE_FROM)) as min_DATE_FROM
,if (max(DATE_TO)=today()+100,null(),date(max(DATE_TO))) as max_DATE_TO
Resident tmp
Group By
EMPL_NO,PERIOD;

Regards