Dears,

I want group by based on sequel of dates.

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.

Hi @Ramadass ,

When you group like that either you need to have condition for each date range or it should be like 

set of records per month. 

What are the conditions on your scenario? 

Above two date ranges have nothing in similar. 

Hi,

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

Thanks in advance...

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,
       (SELECT MAX(date_of_leaving)
        FROM emp_employed_time_row t
        CONNECT BY t.company_id = PRIOR t.company_id
        AND t.emp_no = PRIOR t.emp_no
        AND t.date_of_employment = CASE WHEN PRIOR t.date_of_leaving = to_date('9999-12-31','YYYY-MM-DD') THEN PRIOR t.date_of_leaving ELSE PRIOR t.date_of_leaving + 1 END
        START WITH 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'
AND NOT EXISTS (SELECT 1
                FROM 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.