Search code examples
sqloracle-databasegaps-and-islands

create date range from day based data

i have following source data...

id     date     value
1      01.08.22 a
1      02.08.22 a
1      03.08.22 a
1      04.08.22 b
1      05.08.22 b
1      06.08.22 a
1      07.08.22 a
2      01.08.22 a
2      02.08.22 a
2      03.08.22 c
2      04.08.22 a
2      05.08.22 a

and i would like to have the following output...

id     date_from     date_until    value
1      01.08.22      03.08.22      a
1      04.08.22      05.08.22      b
1      06.08.22      07.08.22      a
2      01.08.22      02.08.22      a     
2      03.08.22      03.08.22      c
2      04.08.22      05.08.22      a

Is this possible with Oracle SQL? Which functions do I need for this?

Solution

  • Based on the link provided by @astentx, try this solution:

    SELECT 
    id, MIN("date") AS date_from, MAX("date") AS date_until, MAX(value) AS value
FROM (
    SELECT 
        t1.*,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY "date") -
        ROW_NUMBER() OVER(PARTITION BY id, value ORDER BY "date") AS rn
    FROM yourtable t1
) 
GROUP BY id, rn

    See db<>fiddle