2
declare @t table (scanCode varchar(6), dates datetime, flag varchar(1))

insert @t ( scanCode, dates, flag)
select '182086','01 Jul 2020','P' union all
select'182086','02 Jul 2020','P' union all
select'182086','03 Jul 2020','A' union all
select'182086','04 Jul 2020','A' union all
select'182086','06 Jul 2020','P' union all
select'182086','07 Jul 2020','P' union all
select'182086','08 Jul 2020','P' union all
select'182086','09 Jul 2020','P' union all
select'182086','10 Jul 2020','A' union all
select'182086','11 Jul 2020','A' union all
select'182086','13 Jul 2020','A' union all
select'182086','14 Jul 2020','A'


select scanCode
    , dates
    , flag
    , prn = row_number() over (partition by scanCode,flag order by scanCode, dates) 
from @t t
order by t.dates

Currently the result is:

scanCode    dates   flag    prn
182086      2020/07/01  P   1
182086      2020/07/02  P   2
182086      2020/07/03  A   1
182086      2020/07/04  A   2
182086      2020/07/06  P   3
182086      2020/07/07  P   4
182086      2020/07/08  P   5
182086      2020/07/09  P   6
182086      2020/07/10  A   3
182086      2020/07/11  A   4
182086      2020/07/13  A   5
182086      2020/07/14  A   6

I want the result to be:

scanCode    dates   flag    prn
182086      2020/07/01  P   1
182086      2020/07/02  P   2
182086      2020/07/03  A   1
182086      2020/07/04  A   2
182086      2020/07/06  P   1
182086      2020/07/07  P   2
182086      2020/07/08  P   3
182086      2020/07/09  P   4
182086      2020/07/10  A   1
182086      2020/07/11  A   2
182086      2020/07/13  A   3
182086      2020/07/14  A   4

scancode, dates is primary key in my table.

1

SQL Server 2008R2 is pretty limited in its windowing functions, no lag/lead functionality, and ORDER BY can only be used with ROW_NUMBER(). So the usual techniques to solve this are not readily available.

I'm going to assume your primary key is (ScanCode,Dates), if it isn't this won't return consistent results.

SELECT
  curr.ScanCode
 ,curr.Dates
 ,curr.Flag
 ,prn = ROW_NUMBER() OVER (PARTITION BY curr.ScanCode, pr.Dates ORDER BY curr.Dates)
FROM
  @t curr
LEFT JOIN
  @t pr --Get maximum date prior to current date where Flag did not match
    ON pr.ScanCode = curr.ScanCode
        AND pr.Dates =
          (
            SELECT
              MAX(Dates)
            FROM
              @t
            WHERE
              ScanCode = curr.ScanCode
                AND Flag <> curr.Flag
                AND Dates < curr.Dates
          )

This works because the dates for a contiguous block of Flag will all have the same prior date value:

ScanCode    Dates       Flag    PriorDate
182086      2020-07-01  P       NULL
182086      2020-07-02  P       NULL
182086      2020-07-03  A       2020-07-02
182086      2020-07-04  A       2020-07-02
182086      2020-07-06  P       2020-07-04
182086      2020-07-07  P       2020-07-04
182086      2020-07-08  P       2020-07-04
182086      2020-07-09  P       2020-07-04
182086      2020-07-10  A       2020-07-09
182086      2020-07-11  A       2020-07-09
182086      2020-07-13  A       2020-07-09
182086      2020-07-14  A       2020-07-09

So if your clustered index is chosen properly, this is one table scan plus two seeks per row, and one sort for the PARTITION BY. Not great, but even with LAG/LEAD you'd probably not create anything more efficient.

Other advice:

  1. Dates isn't a good column name as the column stores a single value, not plural. Additionally, it's not very descriptive of what the column represents - is this a date of a certain event? Entry date? If you can rename it, do. Also, if the values will always be dates, use the DATE datatype.
  2. The primary key in this instance really should be (ScanCode,Dates) if it isn't already. Not only is this necessary to guarantee consistent results, but the join to get the prior value will be a seek on the clustered index. To test, declare a primary key on your table variable and compare the execution plans with and without.
1

Another way to solve this is to think of it as a variation of the gaps and islands pattern:

SELECT *, diff = (N.rn1 - N.rn2)
FROM 
(
    SELECT 
        *, 
        rn1 = ROW_NUMBER() OVER (
            PARTITION BY T.scanCode 
            ORDER BY T.dates),
        rn2 = ROW_NUMBER() OVER (
            PARTITION BY T.scanCode 
            ORDER BY T.flag, T.dates)
    FROM @t AS T
) AS N;

That output gives us a way to identify groups:

groups

All that is left to do is number rows within each group. The full query is:

WITH
    N AS 
    (
        -- Previous code
        SELECT 
            *, 
            rn1 = ROW_NUMBER() OVER (
                PARTITION BY T.scanCode 
                ORDER BY T.dates),
            rn2 = ROW_NUMBER() OVER (
                PARTITION BY T.scanCode 
                ORDER BY T.flag, T.dates)
        FROM @t AS T
    )
SELECT
    N.scanCode,
    N.dates,
    N.flag,
    prn = ROW_NUMBER() OVER (
        PARTITION BY N.scanCode, (N.rn1 - N.rn2)
        ORDER BY N.dates)
FROM N;

The output is:

output

Execution plan:

plan

db<>fiddle online demo

Not the answer you're looking for? Browse other questions tagged or ask your own question.