Oracle - Get min & max date from group of rows

Question

Initial data :

select t.AGENHI, t.TACTHI, t.DTEFHI, t.DTFIHI            
      from mytable t
      where agenhi = '81000040' ;

AGENHI      TACTHI   DTEFHI     DTFIHI            
81000040    1        24/02/92   08/03/92
81000040    1        09/03/92   28/02/93
81000040    1        01/03/93   31/05/97
81000040    0,8      01/06/97   31/12/97
81000040    1        01/01/98   31/12/98
81000040    1        01/01/99

Using this query :

SELECT AGENHI, 
DECODE(TACTHI, 0.05, '005', 0.07, '007', 0.1, '010', 0.137, '013', 0.15, 
'015', 0.2, '020', 0.21, '021', 0.23, '023', 0.25, '025', 0.3, '030', 
0.34, '034', 0.4, '040', 0.45, '045', 0.5, '050', 0.6, '060', 0.63, 
'063',0.7, '070', 0.75, '075', 0.8,'080', 0.84, '084',0.9, '090', 1, '100', 
TACTHI) as QUOTITE,
dtefhi as START_DATE,
DECODE(LEAD (DTFIHI, 1) OVER (ORDER BY DTFIHI NULLS LAST) ,null, 
to_date('31122099','ddmmyyyy'), LEAD (DTFIHI, 1) OVER (ORDER BY DTFIHI NULLS 
LAST)) AS END_DATE
FROM MYTABLE 
WHERE AGENHI = '81000040' AND DTFIHI IS NOT NULL;

I get :

AGENHI      QUOTITE     START_DATE    END_DATE
81000040    100         08/03/92      28/02/93
81000040    100         28/02/93      31/05/97
81000040    100         31/05/97      31/12/97
81000040    080         31/12/97      31/12/98
81000040    100         31/12/98      31/12/99

But i need to group "QUOTITE" when next rows is the same and display the start date of the first row and the date of last rows.

Expected result :

AGENHI      QUOTITE     START_DATE    END_DATE
81000040    100         24/02/92      31/12/97
81000040    080         01/06/97      31/12/98
81000040    100         01/01/98      31/12/99

Solution provided by gordon :

select agenhi, tacthi, min(dtfihi) as start_date, lead(max(dtfihi)) over (partition by agenhi, seqnum - seqnum_2 order by max(dtfihi)) as end_date from (select t.*, row_number() over (partition by agenhi order by dtfihi) as seqnum, row_number() over (partition by agenhi, tacthi order by dtfihi) as seqnum_2 from HIA@CHRONOS_TO_S2.WORLD t where agenhi = '81000040' and dtfihi IS NOT NULL ) t group by agenhi, (seqnum - seqnum_2), tacthi;

Result :

81000040    1   08/03/92   null 
81000040    1   31/12/98   null   
81000040    0,8 31/12/97   null

How can i get the end_date ? I will look if i found the solution thank you !

This kind of problem is called gaps and islands. There are many requests on this on SO you can look up. You'll have to get quotite first, then subtract row_number() over (partition by quotite order by dtfihi) from row_number() over (order by dtfihi) to get a group key. Then aggregate getting min and max date per group key. At last use LEAD to adjust the end dates. — Thorsten Kettner

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-08-03T09:18:17

You have a gaps-and-islands problem. I would start with the original data, so something like this:

select agenhi, tacthi, min(dtfihi) as start_date,
       lead(min(dtfihi)) over (partition by agenhi order by min(dtfihi)) as end_date
from (select t.*,
             row_number() over (partition by agenhi, order by dtfihi) as seqnum,
             row_number() over (partition by agenhi, tacthi order by dtfihi) as seqnum_2
      from mytable t
      where agenhi = '81000040' an dtfihi IS NOT NULL
     ) t
group by agenhi, (seqnum - seqnum_2), tacthi;

You can add the decode() logic (which I would write as a case expression), but it doesn't really change the solution.

Oracle - Get min & max date from group of rows

2 Answers