Reputation: 31
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
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
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