ROW_NUMBER() with BETWEEN SQL Server 2012

Question

I need some guidance with using ROW_NUMBER() along with BETWEEN in SQL Server 2012.

I have written the following code:

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

The purpose is to tag those locationCode BETWEEN MIN(startDate) AND EndDate with 1 ELSE 0.

How can I do that?

I have added a table : this is how it looks right now

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

Objective:

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

The last row for column New is zero because it is outside the EndDate.

I am not sure what other way to go to resolve the problem I have been working on since this morning. Basically, that is all I need to do. How to code it, I am finding it a bit hard. — user3197575, Aug 11 '15 at 19:16
Please include examples. Values are usually always bigger or equal to min values, and it's quite difficult to figure out what EndDate is — James Z, Aug 11 '15 at 19:17
what are you comparing the `min(startdate)` and `enddate` with? — FutbolFan, Aug 11 '15 at 19:17
Classify each Location that is between the Min(StartDate) AND EndDate, then tag all those rows with 1 else 0 — user3197575, Aug 11 '15 at 19:19
How can a `LocationCode` be compared to a `Date`? That's the part I'm struggling with. — Siyual, Aug 11 '15 at 19:20
@user3197575 How do you know that `The last row for column New... is outside the EndDate`? — FutbolFan, Aug 11 '15 at 19:25
Your example even has duplicate rows, and doesn't clarify the question at all. — James Z, Aug 11 '15 at 19:27
@FutbolFan That is the condition I have trying to create.The LocationCode should only be valid between specific StartDate and EndDate. If it is, then 1 else 0. — user3197575, Aug 11 '15 at 19:27
Looks like you asked this question already? Although it has totally different explanations... http://stackoverflow.com/questions/31942653/data-manipulation-row-number-sql-server — James Z, Aug 11 '15 at 19:31
And also this, which is again about location codes and dates: http://stackoverflow.com/questions/31903349/comparing-current-month-and-previous-months-rows-on-a-column-sql-server-2012 — James Z, Aug 11 '15 at 19:34

score 2 · Answer 1 · answered Aug 11 '15 at 19:27

You don't need a ROW_NUMBER() for this. You can do this with a CROSS APPLY.

I'm assuming, based on your sample data, that the datatypes for these fields are 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

score 1 · Answer 2 · answered Aug 11 '15 at 19:32

Alternatively, you could do simple group by and join back the table like this:

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

ROW_NUMBER() with BETWEEN SQL Server 2012

2 Answers2