create date range from day based data-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

WITH CTE (id, dateD,valueD)
AS
(
   SELECT 1,    TO_DATE('01.08.22','DD.MM.YY'), 'a' FROM DUAL UNION ALL
   SELECT 1,     TO_DATE('02.08.22','DD.MM.YY'), 'a'FROM DUAL UNION ALL
   SELECT 1,     TO_DATE('03.08.22','DD.MM.YY'), 'a'FROM DUAL UNION ALL
   SELECT 1,     TO_DATE('04.08.22','DD.MM.YY'), 'b'FROM DUAL UNION ALL
   SELECT 1,     TO_DATE('05.08.22','DD.MM.YY'), 'b'FROM DUAL UNION ALL
   SELECT 2,     TO_DATE('01.08.22','DD.MM.YY'), 'a'FROM DUAL UNION ALL
   SELECT 2,     TO_DATE('02.08.22','DD.MM.YY'), 'a'FROM DUAL UNION ALL
   SELECT 2,     TO_DATE('03.08.22','DD.MM.YY'), 'c'FROM DUAL
)
 SELECT C.ID,C.VALUED,MIN(C.DATED)AS MIN_DATE,MAX(C.DATED)AS MAX_DATE
 FROM CTE C
 GROUP BY C.ID,C.VALUED
 ORDER BY C.ID

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=47c87d60445ce262cd371177e31d5d63