Island and RunningTotal Sample



 create table test ([week] int, Barcode bigint, Product varchar(50))
insert into test values(1,123456789012,'Prod A')
,(2,123456789012,'Prod A')
,(3,123456789012,'Prod A')
,(4,123456789012,'Prod B')
,(5,123456789012,'Prod A')
,(6,123456789012,'Prod X')
,(7,123456789012,'Prod Y')
,(8,123456789012,'Prod Y')
,(9,123456789012,'Prod A')
,(10,123456789012,'Prod A')

;with mycte as (
select [week],Barcode,Product, row_number() Over(Order by [week]) rn,
row_number() Over(Order by [week]) - row_number() Over(Partition By Product Order by [week]) delta

from test)

,mycte1 as (select [week],Barcode,Product, rn
, Case WHEN rank() Over(Partition by delta Order By [week])>1 Then 0 Else 1 ENd GenCode from mycte )

, mycte2 AS
(
 SELECT [week],Barcode,rn,Product, GenCode
 FROM mycte1
 WHERE rn = 1
 UNION ALL
 SELECT n.[week],n.Barcode,n.rn,n.Product, m.GenCode + n.GenCode
 FROM mycte2 m INNER JOIN mycte1 n
 ON n.rn = m.rn + 1
)
SELECT [week],Barcode, Product, GenCode FROM mycte2
 ORDER BY [week]

OPTION (MAXRECURSION 10000);
drop table test

/*
week Barcode Product GenCode
1 123456789012 Prod A 1
2 123456789012 Prod A 1
3 123456789012 Prod A 1
4 123456789012 Prod B 2
5 123456789012 Prod A 3
6 123456789012 Prod X 4
7 123456789012 Prod Y 5
8 123456789012 Prod Y 5
9 123456789012 Prod A 6
10 123456789012 Prod A 6

*/
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s