StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This one uses only one table scan and works across years. It's better though to model your month and year column as only one date datatype column:</p> <pre><code>SQL> create table tbl (id,month,year,value) 2 as 3 select 100,12,2007,80 from dual union all 4 select 100,1,2008,80 from dual union all 5 select 100,2,2008,80 from dual union all 6 select 100,3,2008,90 from dual union all 7 select 100,4,2008,80 from dual union all 8 select 200,12,2007,50 from dual union all 9 select 200,1,2008,50 from dual union all 10 select 200,2,2008,40 from dual union all 11 select 200,3,2008,50 from dual union all 12 select 200,4,2008,50 from dual union all 13 select 200,5,2008,50 from dual 14 / Tabel is aangemaakt. SQL> select id 2 , mod(min(year*12+month-1),12)+1 startmonth 3 , trunc(min(year*12+month-1)/12) startyear 4 , mod(max(year*12+month-1),12)+1 endmonth 5 , trunc(max(year*12+month-1)/12) endyear 6 , value 7 from ( select id 8 , month 9 , year 10 , value 11 , max(rn) over (partition by id order by year,month) maxrn 12 from ( select id 13 , month 14 , year 15 , value 16 , case lag(value) over (partition by id order by year,month) 17 when value then null 18 else rownum 19 end rn 20 from tbl 21 ) inner 22 ) 23 group by id 24 , maxrn 25 , value 26 order by id 27 , startyear 28 , startmonth 29 / ID STARTMONTH STARTYEAR ENDMONTH ENDYEAR VALUE ---------- ---------- ---------- ---------- ---------- ---------- 100 12 2007 2 2008 80 100 3 2008 3 2008 90 100 4 2008 4 2008 80 200 12 2007 1 2008 50 200 2 2008 2 2008 40 200 3 2008 5 2008 50 6 rijen zijn geselecteerd. </code></pre> <p>Regards, Rob.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload