SQL查询查找单个，已排序的记录

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.

这是我试图解决的问题。我有一个表,我存储一个StartExclusionDate和EndExclusionDate并为我的对象设置ApplyDate(称为排除)。表中可以有许多ExtId条目。

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.

我想从上一个连续记录中获取ApplyDate,其中传递给查询的日期包含在StartExclusionDate和EndExclusionDate之间。

So here is the table I created in SQL 2005:

这是我在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

我将传递2个参数,@ ExtId和@dtDate。我想根据以下条件检索的记录:@ExtId = 101,@ ddDate ='6/15/2008'将是ID = 4

The logic is this:

逻辑是这样的:

• Get all records for ExtId = 101 and where ApplyDate <= '6/15/2008', sort the ApplyDate DESC

获取ExtId = 101和ApplyDate <='6/15/2008'的所有记录,对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

如果第一个记录的开始日期和结束日期在'6/15/2008'之间,请通过检查下一条记录查找最初设置排除的记录

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

我想找到一种没有CURSOR的方法,是否可以使用查询或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

1 个解决方案

#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