Search code examples
sqloracledatetimegaps-and-islands

Create time intervals based on values in one column / SQL Oracle

I need to create query that will return time intervals from table, that has attributes for (almost) every day.
The original table looks like the following:

Person | Date       | Date_Type
-------|------------|----------
Sam    | 01.06.2020 |  Vacation
Sam    | 02.06.2020 |  Vacation
Sam    | 03.06.2020 |  Work
Sam    | 04.06.2020 |  Work
Sam    | 05.06.2020 |  Work
Frodo  | 01.06.2020 |  Work
Frodo  | 02.06.2020 |  Work
.....

And the desired should look like:

Person | Date_Interval         | Date_Type
-------|-----------------------|----------
Sam    | 01.06.2020-02.06.2020 |  Vacation
Sam    | 03.06.2020-05.06.2020 |  Work
Frodo  | 01.06.2020-02.06.2020 |  Work
.....

Will be grateful for any idea :)

Solution

  • This reads like a gaps-and-island problem. Here is one approach:

    select person, min(date) startdate, max(date) enddate, date_type
from (
    select t.*,
        row_number() over(partition by person order by date) rn1,
        row_number() over(partition by person, date_type order by date) rn2
    from mytable t
) t
group by person, date_type, rn1 - rn2

    This also works if not all dates are contiguous (since you stated that you have almost all dates, I understood you don't have them all).