StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I got it to work as follows. It is heavy on analytic functions and is Oracle specific.</p> <pre><code>select distinct id, value, decode(startMonth, null, lag(startMonth) over(partition by id, value order by startMonth, endMonth), --if start is null, it's an end so take from the row before startMonth) startMonth, decode(endMonth, null, lead(endMonth) over(partition by id, value order by startMonth, endMonth), --if end is null, it's an start so take from the row after endMonth) endMonth from ( select id, value, startMonth, endMonth from( select id, value, decode(month+1, lead(month) over(partition by id,value order by month), null, month) startMonth, --get the beginning month for each interval decode(month-1, lag(month) over(partition by id,value order by month), null, month) endMonth --get the end month for each interval from Tbl ) a where startMonth is not null or endMonth is not null --remain with start and ends only )b </code></pre> <p>It might be possible to simplify some of the inner queries somewhat</p> <p>The inner query checks if the month is a first/last month of the interval as follows: if the month + 1 == the next month (lag) for that grouping, then since there is a next month, this month is obviously not the end month. Otherwise, it <em>is</em> the last month of the interval. The same concept is used to check for the first month. </p> <p>The outer query first filters out all rows that are not either start or end months (<code>where startMonth is not null or endMonth is not null</code>). Then, each row is either a start month or an end month (or both), determined by whether start or end is not null). If the month is a start month, get the corresponding end month by getting the next (lead) endMonth for that id,value ordered by endMonth, and if it is an endMonth get the startMonth by looking for the previous startMonth (lag)</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