Hello i've got problem with selecting data from table..
Source table look like this:
TAB1:
ID,DATE1,DATE_FROM,DATE_TO,TYPE
171367,2002-08-05,2002-08-05,2002-11-28,N
171366,2002-08-05,2002-11-29,2002-12-31,N
171365,2002-08-05,2003-01-01,2003-12-31,N
171364,2002-08-05,2004-01-01,2004-02-29,N
171363,2002-08-05,2004-03-01,2004-06-30,O
172359,2002-08-05,2004-07-01,2005-02-02,N
173739,2002-08-05,2005-02-03,2006-10-31,N
178377,2002-08-05,2006-11-01,2006-12-31,N
178901,2002-08-05,2007-01-01,2007-10-31,N
204761,2002-08-05,2007-11-01,2010-04-01,N
214343,2002-08-05,2010-04-02,2012-07-31,N
220408,2002-08-05,2012-08-01,2015-09-30,N
237282,2002-08-05,2015-10-01,2016-03-31,N
237283,2002-08-05,2016-04-01,2016-04-30,N
244695,2002-08-05,2016-05-01,2016-05-31,N
244696,2002-08-05,2016-06-01,2016-09-30,N
237284,2002-08-05,2016-10-01,2017-01-31,N
248037,2002-08-05,2017-02-01,2017-02-28,N
249605,2002-08-05,2017-03-01,2017-09-07,N
252105,2002-08-05,2017-09-08,2018-01-31,N
255042,2002-08-05,2018-02-01,2018-03-31,N
256031,2002-08-05,2018-04-01,2018-06-30,N
256032,2002-08-05,2018-07-01,2019-01-31,N
262573,2002-08-05,2019-02-01,,N
And a need to select min date_from and max date to while type is the same in time sequence
so destination table should look like that:
TAB2:
ID,DATE1,DATE_FROM,DATE_TO,TYPE
171367,2002-08-05,2002-08-05,2004-02-29,N
171363,2002-08-05,2004-03-01,2004-06-30,O
172359,2002-08-05,2004-07-01,,N
I've try this solution:
TAB1_1:
load *,
if (isnull(DATE_TO),999999,DATE_TO) as TMP_DATE_TO,
"ID"&'#'&"TYPE" as KEY,
RESIDENT TAB1:
NoConcatenate
tmp2:
LOAD
KEY,
date(MIN(DATE_FROM)) as Min_DATE_FROM,
if (max(TMP_DATE_TO)=999999,null(),date(max(TMP_DATE_TO))) as Max_DATE_TO
Resident TAB1_1
Group By
KEY;
Left Join (TAB1_1)
LOAD
KEY,
Min_DATE_FROM,
Max_DATE_TO
Resident tmp2;
drop table tmp2;
BUT results was:
ID,DATE1,DATE_FROM,DATE_TO,TYPE
172359,2002-08-05,2004-07-01,,N
171363,2002-08-05,2004-03-01,2004-06-30,O
Please help
