StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In case we can, as you state, safely assume that next record equals previous record + 1 day, then we can chain these records up: </p> <p><a href="http://sqlfiddle.com/#!4/5b0eb/7" rel="nofollow">SQL Fiddle</a></p> <p><strong>Oracle 11g R2 Schema Setup</strong>:</p> <pre><code>CREATE TABLE t (employee int, effective_dt timestamp, expiration_dt timestamp, col_a varchar2(7), col_b varchar2(7), col_c varchar2(7)) ; INSERT ALL INTO t (employee, effective_dt, expiration_dt, col_a, col_b, col_c) VALUES (0001, '01-Dec-2012 12:00:00 AM', '04-Dec-2012 12:00:00 AM', 'value_a', 'value_a', 'value_a') INTO t (employee, effective_dt, expiration_dt, col_a, col_b, col_c) VALUES (0001, '05-Dec-2012 12:00:00 AM', '06-Dec-2012 12:00:00 AM', 'value_a', 'value_a', 'value_a') INTO t (employee, effective_dt, expiration_dt, col_a, col_b, col_c) VALUES (0001, '07-Dec-2012 12:00:00 AM', '10-Dec-2012 12:00:00 AM', 'value_a', 'value_a', 'value_a') INTO t (employee, effective_dt, expiration_dt, col_a, col_b, col_c) VALUES (0001, '11-Dec-2012 12:00:00 AM', '17-Dec-2012 12:00:00 AM', 'value_a', 'value_b', 'value_a') INTO t (employee, effective_dt, expiration_dt, col_a, col_b, col_c) VALUES (0001, '18-Dec-2012 12:00:00 AM', '19-Dec-2012 12:00:00 AM', 'value_a', 'value_b', 'value_a') INTO t (employee, effective_dt, expiration_dt, col_a, col_b, col_c) VALUES (0001, '20-Dec-2012 12:00:00 AM', '31-Dec-2012 12:00:00 AM', 'value_a', 'value_a', 'value_a') SELECT * FROM dual ; </code></pre> <p><strong>Query 1</strong>:</p> <pre><code>select employee, min(effective_dt), max(expiration_dt), col_a, col_b, col_c from ( select t.*, case when col_a = lag(col_a) over (partition by employee order by expiration_dt asc) and col_b = lag(col_b) over (partition by employee order by expiration_dt asc) and col_c = lag(col_c) over (partition by employee order by expiration_dt asc) then 0 else 1 end start_of_chain from t ) connect by effective_dt = prior expiration_dt + 1 and start_of_chain = 0 start with start_of_chain = 1 group by connect_by_root(effective_dt), employee, col_a, col_b, col_c order by 2 </code></pre> <p><strong><a href="http://sqlfiddle.com/#!4/5b0eb/7/0" rel="nofollow">Results</a></strong>:</p> <pre><code>| EMPLOYEE | MIN(EFFECTIVE_DT) | MAX(EXPIRATION_DT) | COL_A | COL_B | COL_C | -------------------------------------------------------------------------------------------------------------- | 1 | December, 01 2012 00:00:00+0000 | December, 10 2012 00:00:00+0000 | value_a | value_a | value_a | | 1 | December, 11 2012 00:00:00+0000 | December, 19 2012 00:00:00+0000 | value_a | value_b | value_a | | 1 | December, 20 2012 00:00:00+0000 | December, 31 2012 00:00:00+0000 | value_a | value_a | value_a | </code></pre>

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