Beanl
Beanl

Reputation: 31

Oracle - Get min & max date from group of rows

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 !

Upvotes: 2

Views: 1119

Answers (2)

Beanl
Beanl

Reputation: 31

@Boneist , @GordonLinoff Thank you again for your help.

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 QUOTITECPAGE,  
min(dtefhi) as start_date,
nvl(lead(min(dtfihi)) over (partition by agenhi order by min(dtfihi)), 
to_date('31122099','ddmmyyyy')) 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 dtefhi) as seqnum_2
  from HIA@CHRONOS_TO_S2.WORLD t
  where agenhi = '81000040' order by DTEFHI
 ) t
 group by agenhi, (seqnum - seqnum_2), tacthi;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 2

Related Questions