endpoints of "most recent" range in ordered sequence

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tracy (Tracy_at_discussions.microsoft.com)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 11:57:01 -0800

Suppose I have a history table with a key, a version of history, and another
value (which can be null). I need to locate the endpoints of the most recent
range of history records having a set of contiguous null values. An example
follows, along with desired results (and invalid results):

key version optional_val
1 1 10
1 2 null
1 3 null
1 4 15 (most current version of key = 1)
2 1 null
2 2 null
2 3 10
2 4 null
2 5 null
2 6 null
2 7 22
2 8 24 (most current version of key = 2)

For results, I want:
key min_version max_version
1 2 3
2 4 6
(notice how, for key = 2, I do NOT want to go back to that older bunch of
null-valued records).

I've been looking at 2- and 3-way self-joins here, and so far, nothing but
blue smoke coming out of my ears. All suggestions on how to think of this
problem appreciated.

Tracy Slack

Relevant Pages

  • PurgeJobHistory Method-SQLDMO
    ... I want to do is to manage the job history and remove selected job history ... passing the same JobHistoryFilter object to the PurgeJobHistory ... var objJobHistory; ... // I want to remove all the failed history records from the start of the job ...
    (microsoft.public.sqlserver.dts)
  • PurgeJobHistory not constrained by JobHistoryFilter??
    ... I want to do is to manage the job history and remove selected job history ... passing the same JobHistoryFilter object to the PurgeJobHistory ... var objJobHistory; ... // I want to remove all the failed history records from the start of the job ...
    (microsoft.public.sqlserver.programming)
  • Re: hlookups
    ... You have posted in the Mac Excel group. ... The difference is that the history sheet ... and have a drop down or pop up which shows all of the history records from ...
    (microsoft.public.mac.office.excel)
  • hlookups
    ... The difference is that the history sheet ... I would like to be able to select a account number on the first sheet ... and have a drop down or pop up which shows all of the history records from ...
    (microsoft.public.mac.office.excel)