question

You can use the LAG-function to identify if a new group of values occurs, and then use a rolling SUM of the result of the LAG-function to produce group numbers, and then partition by those instead of the flag in your query.

WITH CTE AS (
 SELECT scancode, dates,flag, 
  CASE 
  WHEN LAG(flag,1) OVER(PARTITION BY scanCode ORDER BY dates) = flag 
  THEN 0
  ELSE 1
  END AS isNewGroup
 FROM @t
), CTE2 AS(
 SELECT scancode, dates, flag, SUM(isNewGroup) OVER(PARTITION BY scanCode ORDER BY dates ROWS UNBOUNDED PRECEDING) AS GroupNumber
 FROM CTE
)
SELECT scanCode
 , dates
 , flag
 , prn = row_number() over (partition by scanCode,GroupNumber order by scanCode, dates) 
from cte2 t
order by t.dates

I have sql server 2008

I get error:

Msg 195, Level 15, State 10, Line 67 'LAG' is not a recognized built-in function name. Msg 102, Level 15, State 1, Line 73 Incorrect syntax near 'ROWS'.

Somebody please reply

--alter proc question as

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
*/

@mateen

@Magnus Ahlkvist

The "Difference between two series" to build "groups" makes this a fairly easy problem even in SQL Server 2008. This works as far back as SQL Server 2005 and seems to be a bit of a "Black Art" that people are quickly forgetting.

Never forget the "old ways" because you can never tell when they might become useful or help you think about a problem in a different way. :D If you don't know the "old ways", you should learn them from us old dudes before we no longer exist. ;)

   WITH cteEnumerateGroup AS
(
 SELECT *
        ,Grp = ROW_NUMBER() OVER (ORDER BY ScanCode, Dates, Flag)
             - ROW_NUMBER() OVER (PARTITION BY Flag ORDER BY ScanCode, Dates)
   FROM @t
)
 SELECT ScanCode, Dates, Flag
        ,Prn = ROW_NUMBER() OVER (PARTITION BY ScanCode, Flag, Grp ORDER BY Dates)
   FROM cteEnumerateGroup
  ORDER BY ScanCode, Dates, Flag, Prn
;

There is another way to do this that works really well for huge tables and with fewer sorts called the "Quirky Update" but too many people poo-poo it because they don't understand it well enough to know that, despite what people at MS have said, it works and it won't break.