valid from: 1/1/2019 to valid_to: 12/18/2020 should be one record and it is sequel of dates.
and valid from: 3/1/2021 to valid_to: 3/31/2021 should be another record.
so the result will be two records.
how to partition based on sequel of dates.
Best answer by Tomas Ruderfelt
I do not exactly know which table you are using but here is an example.
Table data:
Select statement to find interval of intervals:
SELECT company_id, emp_no,date_of_employment,
(SELECTMAX(date_of_leaving)
FROM emp_employed_time_row t
CONNECTBY t.company_id = PRIOR t.company_id
AND t.emp_no = PRIOR t.emp_no
AND t.date_of_employment = CASEWHENPRIOR t.date_of_leaving = to_date('9999-12-31','YYYY-MM-DD') THENPRIOR t.date_of_leaving ELSEPRIOR t.date_of_leaving + 1ENDSTARTWITH t.company_id = e.company_id
AND t.emp_no = e.emp_no
AND t.date_of_employment = e.date_of_employment) date_of_leaving
FROM emp_employed_time_row e
WHERE company_id = '300'ANDNOTEXISTS (SELECT1FROM emp_employed_time_row x
WHERE x.company_id = e.company_id
AND x.emp_no = e.emp_no
AND e.date_of_employment -1= x.date_of_leaving)
Short explanation:
The main select fetches all start of intervals you want. Then the end date is fetched by a query in the SELECT part.
I had to do special handling of date 9999-21-31 due to the data I had. Not needed if you do not have/will have that kind of data.
If you see first three rows of valid from and valid_to it is sequel, no gaps in dates but between third row valid_to and fourth row valid from there is gap in dates.
I want to show first three rows as one record if I take as min(valid_from) and max(valid_to) and fourth row as one record
I do not exactly know which table you are using but here is an example.
Table data:
Select statement to find interval of intervals:
SELECT company_id, emp_no,date_of_employment,
(SELECTMAX(date_of_leaving)
FROM emp_employed_time_row t
CONNECTBY t.company_id = PRIOR t.company_id
AND t.emp_no = PRIOR t.emp_no
AND t.date_of_employment = CASEWHENPRIOR t.date_of_leaving = to_date('9999-12-31','YYYY-MM-DD') THENPRIOR t.date_of_leaving ELSEPRIOR t.date_of_leaving + 1ENDSTARTWITH t.company_id = e.company_id
AND t.emp_no = e.emp_no
AND t.date_of_employment = e.date_of_employment) date_of_leaving
FROM emp_employed_time_row e
WHERE company_id = '300'ANDNOTEXISTS (SELECT1FROM emp_employed_time_row x
WHERE x.company_id = e.company_id
AND x.emp_no = e.emp_no
AND e.date_of_employment -1= x.date_of_leaving)
Short explanation:
The main select fetches all start of intervals you want. Then the end date is fetched by a query in the SELECT part.
I had to do special handling of date 9999-21-31 due to the data I had. Not needed if you do not have/will have that kind of data.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.