title: SQL query to find single, sorted record
body: <p>Here is the problem I am trying to solve.  I have a table where I store a StartExclusionDate and EndExclusionDate and set an ApplyDate for my object (called Exclusion).  There can be many ExtId entries in the table.</p>
  
  <p>I want to get the ApplyDate from the last continuous record where a date passed to the query is included between the StartExclusionDate and EndExclusionDate.</p>
  
  <p>So here is the table I created in SQL 2005:</p>
  
  <pre><code>Id int PK
  ExtId int FK
  StartExclusionDate datetime null
  EndExclusionDate datetime null
  ApplyDate not null
  </code></pre>
  
  <p>And here is an example dataset:</p>
  
  <pre><code>  ID  |  ExtID  |  StartDate  |  EndDate  |  ApplyDate
  --------------------------------------------------------
     1      101     11/15/2005    3/15/2010   11/15/2005
     2      101                               12/30/2005
     3      101       1/1/2000     1/1/2000     1/1/2006
     4      101       4/1/2008                  4/1/2008
     5      101                    8/1/2010     6/1/2008
     6      101      11/1/2006   12/31/2010    11/1/2009
  </code></pre>
  
  <p>I would be passing 2 parameters, @ExtId and @dtDate.  The record I want to retrieve based on the following conditions: <code>@ExtId = 101, @dtDate = '6/15/2008'</code> would be <strong>ID = 4</strong></p>
  
  <p>The logic is this:</p>
  
  <ul><li>Get all records for ExtId = 101 and where ApplyDate &lt;= '6/15/2008', sort the ApplyDate DESC</li>
  <li>If the first record's Start and End Date are between '6/15/2008', find the record where the Exclusion was originally set by checking the next records</li></ul>
  
  <p>I wanted to find a way to do this without a CURSOR, is it possible to do with a query or CTE?</p>
  
  <p><strong>EDIT:</strong>
  Here it is done with a cursor</p>
  
  <pre><code>IF OBJECT_ID('TempDB..#t_edr','U') IS NOT NULL
    DROP TABLE #t_edr
  
  CREATE TABLE #t_edr
  (
      Id INT NOT NULL,
      ExtId INT NOT NULL,
      StartDate DATETIME NULL,
      EndDate DATETIME NULL,
      ApplyDate DATETIME NOT NULL
  )
  
  DECLARE @ExtID INT, @dtDate DATETIME, @dtReturn DATETIME
  SELECT @ExtID = 101, @dtDate = '6/15/2008'  
  
  SET NOCOUNT ON
  INSERT INTO #t_edr VALUES (1, 101, '11/15/2005', '3/15/2010', '11/15/2005')
  INSERT INTO #t_edr VALUES (2, 101, NULL, NULL, '12/30/2005')
  INSERT INTO #t_edr VALUES (3, 101, '1/1/2000', '1/1/2000', '1/1/2006')
  INSERT INTO #t_edr VALUES (4, 101, '4/1/2008', NULL, '4/1/2008')
  INSERT INTO #t_edr VALUES (5, 101, NULL, '8/1/2010', '6/1/2008')
  INSERT INTO #t_edr VALUES (6, 101, '11/1/2006', '12/31/2010', '6/1/2009')
  SET NOCOUNT OFF
  
  IF EXISTS (
      SELECT EDR.ID FROM #t_edr EDR
      INNER JOIN (SELECT TOP 1 ID FROM #t_edr WHERE ExtId = @ExtID AND ApplyDate &lt;= @dtDate ORDER BY ApplyDate DESC) LatestEDR
      ON EDR.ID = LatestEDR.ID
      WHERE ExtId = @ExtID 
      AND ApplyDate &lt;= @dtDate    
      AND (StartDate IS NULL OR StartDate &lt;= @dtDate)
      AND (EndDate IS NULL OR EndDate &gt;= @dtDate)
      )
      BEGIN
          DECLARE @ID INT, @StartEDR DATETIME, @EndEDR DATETIME, @ApplyDate DATETIME, 
              @CurrentApplyDate DATETIME, @Continue BIT
  
          DECLARE c CURSOR LOCAL FAST_FORWARD FOR     
          SELECT Id, StartDate, EndDate, ApplyDate FROM #t_edr 
          WHERE ExtId = @ExtID AND ApplyDate &lt;= @dtDate ORDER BY ApplyDate DESC
  
          OPEN c
  
          FETCH NEXT FROM c INTO @id, @startedr, @endedr, @ApplyDate
          IF @@FETCH_STATUS &lt;&gt; 0 SET @Continue = 0
          ELSE SET @Continue = 1
  
          SET @CurrentApplyDate = @ApplyDate
          WHILE @Continue = 1
          BEGIN                                   
              IF @StartEDR &gt;= @dtDate OR @EndEDR &lt;= @dtDate
                  SET @Continue = 0
  
              IF @Continue = 1
              BEGIN
                  SET @CurrentApplyDate = @ApplyDate  
                  FETCH NEXT FROM c INTO @id, @startedr, @endedr, @ApplyDate
                  IF @@FETCH_STATUS &lt;&gt; 0 SET @Continue = 0
              END
          END
          CLOSE c
          DEALLOCATE c
  
          SELECT @CurrentApplyDate
      END 
  ELSE
      PRINT 'NOT EXCLUDED'
  
  DROP TABLE #t_edr
  </code></pre>