question

mateen avatar image
mateen asked

Sql Server 2008 , to assign numbers incrementally and re-initialize whenever the value of the column changes

question.txt

sql-server-2008
question.txt (1.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have sql server 2008.

I get error message for your code

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'.

0 Likes 0 ·

Dear Magnus Ahlkvist

I have checked your code in SQL Server 12 and it is perfect.

But I want the same one SQL server 2008

0 Likes 0 ·

Now that's a whole different story, where you'd have to do a left outer self join to look at the previous row in a given sort-order to find out if you're on a new group or not.

0 Likes 0 ·

I've gotten so used to using Window functions for this type of task, I don't from the top of my head even remember how I did it before.
But why do you need it to work on SQL Server 2008? SQL Server 2008 is unsupported since quite some time now. SQL Server 2012 is the lowest version that is still supported.

0 Likes 0 ·
mateen avatar image
mateen answered

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'.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mateen avatar image
mateen answered

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

@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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.