ROW_NUMBER() with BETWEEN SQL Server 2012

Question

我需要一些指导使用ROW_NUMBER()和BETWEEN在SQL Server 2012。

我写了下面的代码:

SELECT DISTINCT
    *,
    CASE WHEN (row_number() OVER BETWEEN (PARTITION BY LocationCode 
                                          ORDER BY startdate ASC) AND EndDate) = 1 
           THEN 1 ELSE 0 
    END AS Testr,
FROM 
    Table 

目的是用1 ELSE 0标记那些locationCode BETWEEN MIN(startDate) AND EndDate。

我该怎么做呢?

我已经添加了一个表:这是它现在的样子

LocationCode    StartDate   EndDate
10808247    20140617    20140701
10808247    20140618    20140701
10808247    20140618    20140701
10808247    20140617    20140701
10808247    20140709    20140801

<<p> 目标/strong>:

LocationCode    StartDate   EndDate   New
10808247    20140617    20140701            1
10808247    20140618    20140701            1
10808247    20140618    20140701            1
10808247    20140617    20140701            1
10808247    20140709    20140801            0

New列的最后一行为零，因为它在EndDate之外。

Answer 1

您不需要ROW_NUMBER()。您可以使用CROSS APPLY。

根据您的示例数据，我假设这些字段的数据类型是INT。

Select  T.LocationCode, T.StartDate, T.EndDate,
        Case When T.StartDate > X.MinEndDate Then 0 Else 1 End As New
From    Table   T
Cross Apply
(
    Select  Min(T2.EndDate) MinEndDate
    From    Table   T2
    Where   T2.LocationCode = T.LocationCode
) X

Answer 2

或者，您可以执行简单的分组并返回表，如下所示:

SELECT T.LocationCode
      ,T.StartDate
      ,T.EndDate
      ,CASE 
         WHEN T.StartDate > t2.MinEndDate
            THEN 0
       ELSE 1
       END AS New
FROM test T
INNER JOIN (
    SELECT locationcode
           ,Min(EndDate) MinEndDate
    FROM test
    GROUP BY locationcode
    ) T2 ON T2.LocationCode = T.LocationCode;

SQL Fiddle Demo