Select only Contiguous Records in DB2 SQL

Question

So i have a table of readings (heavily simplified version below) - sometimes there is a break in the reading history (see the record i have flagged as N) - The 'From Read' should always match a previous 'To Read' or the 'To Read' should always match a later 'From Read' BUT I want to only select records as far back as the first 'break' in the reads.

How would i write a query in DB2 SQL to only return the rows flagged with a 'Y'?

EDIT: The contiguous flag is something i have added manually to represent the records i would like to select, it does not exist on the table.

ID  From        To          Contiguous
ABC 01/01/2014  30/06/2014  Y
ABC 01/06/2013  01/01/2014  Y
ABC 01/05/2013  01/06/2013  Y
ABC 01/01/2013  01/02/2013  N
ABC 01/10/2012  01/01/2013  N

Thanks in advance! J

The answer to your question is `where Contiguous = 'Y'`. But, I think more is going on. You should edit the question, clarifying the explanation, and showing the results that you want to get back. — Gordon Linoff, Dec 05 '14 at 12:48
sorry, i have added the contiguous flag myself to make it clear what records i want to select, there is no flag on the table — JungleJme, Dec 05 '14 at 13:03

score 0 · Answer 1 · answered Dec 05 '14 at 13:26

0

you will need a recursive select something like that:

WITH RECURSIVE
 contiguous_intervals(start, end) AS (
    select start, end
    from intervals
    where end = (select max(end) from intervals)
 UNION ALL
    select i.start, i.end
    from contiguous_intervals m, intervals i
    where i.end = m.start
)
select * from contiguous_intervals;

answered Dec 05 '14 at 13:26

syllabus

581
3
9

and that's why i was stuck, that statement is well beyond me! This is a table with 600,000 records in so i will need to test this statement out and will come back to tick the post if its all good. Thank you so much for the quick reply – JungleJme Dec 05 '14 at 13:31

score 0 · Answer 2 · answered Dec 05 '14 at 13:30

You can do this with lead(), lag(). I'm not sure what the exact logic is for your case, but I think it is something like:

select r.*,
       (case when (prev_to = from or prev_to is null) and
                  (next_from = to or next_from is null)
             then 'Y'
             else 'N'
        end) as Contiguous
from (select r.*, lead(from) over (partition by id order by from) as next_from,
             lag(to) over (partition by id order by to) as prev_to
      from readings r
     ) r;

Select only Contiguous Records in DB2 SQL

2 Answers2