SQL query to find single, sorted record

Question

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.

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.

So here is the table I created in SQL 2005:

Id int PK
ExtId int FK
StartExclusionDate datetime null
EndExclusionDate datetime null
ApplyDate not null

And here is an example dataset:

  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

I would be passing 2 parameters, @ExtId and @dtDate. The record I want to retrieve based on the following conditions: @ExtId = 101, @dtDate = '6/15/2008' would be ID = 4

The logic is this:

• Get all records for ExtId = 101 and where ApplyDate <= '6/15/2008', sort the ApplyDate DESC
• 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

I wanted to find a way to do this without a CURSOR, is it possible to do with a query or CTE?

EDIT: Here it is done with a cursor

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 <= @dtDate ORDER BY ApplyDate DESC) LatestEDR
 ON EDR.ID = LatestEDR.ID
 WHERE ExtId = @ExtID 
 AND ApplyDate <= @dtDate  
 AND (StartDate IS NULL OR StartDate <= @dtDate)
 AND (EndDate IS NULL OR EndDate >= @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 <= @dtDate ORDER BY ApplyDate DESC

  OPEN c

  FETCH NEXT FROM c INTO @id, @startedr, @endedr, @ApplyDate
  IF @@FETCH_STATUS <> 0 SET @Continue = 0
  ELSE SET @Continue = 1

  SET @CurrentApplyDate = @ApplyDate
  WHILE @Continue = 1
  BEGIN         
   IF @StartEDR >= @dtDate OR @EndEDR <= @dtDate
    SET @Continue = 0

   IF @Continue = 1
   BEGIN
    SET @CurrentApplyDate = @ApplyDate 
    FETCH NEXT FROM c INTO @id, @startedr, @endedr, @ApplyDate
    IF @@FETCH_STATUS <> 0 SET @Continue = 0
   END
  END
  CLOSE c
  DEALLOCATE c

  SELECT @CurrentApplyDate
 END 
ELSE
 PRINT 'NOT EXCLUDED'

DROP TABLE #t_edr

Answer 1

select top 1 * from table
where ApplyDate <= '4/15/2008'
and (startdate is null or startdate<= '4/15/2008')
and (enddate is null or enddate >= '4/15/2008')
and extid = '101'
order by  ApplyDate desc