Re: Returning Range of values where gaps exist

From: Tim Frawley <tim_frawley_at_fishgame.state.ak.us>
Date: 28 Apr 2003 10:57:51 -0700
Message-ID: <bfb6a0a.0304280957.2999a818@posting.google.com>

Rene,

I managed to get this to work.

select distinct start_, end_, reader from (   select
    first_value(specimen_id) over (partition by diff) start_,     last_value(specimen_id) over (partition by diff) end_,     reader,
    row_number() over (partition by diff order by specimen_id) r   from (
    select
      row_number() over(partition by reader order by specimen_id) - specimen_id diff,

      reader,
      specimen_id
    from
      reading r1
    where 
      sample_id = '200201049' and 
      reading_number = 1 and 
      specimen_id not in ( select 
                             specimen_id 
                           from 
                             reading 
                           where 
                             reading.year=r1.year 
                             and reading.sample_id=r1.sample_id 
                             and reading.specimen_id=r1.specimen_id 
                             and reading_number = 2 
                          )

  )
) where r = 1
order by start_;

However, this will return only one SAMPLE_IDs worth of data. I haven't figured out yet how to return each Sample IDs records yet but I am hot on the trail.

I want to thank you for your help. I am much futher along that I would have been by myself!

Sincerely,

Tim Received on Mon Apr 28 2003 - 12:57:51 CDT