# 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

*/
```