Here is another addition to the code, identifying the gaps more efficiently.

😎

USE TESTDB;

GO

;WITH BASE_DATA AS

(

SELECT

ST.STG_ID

,ST.[Description] AS SKU

,YK.rn - RANK() OVER

(

PARTITION BY Description

ORDER BY WEEK

) AS WN_OFFSET

,YK.weekx

,YK.rn

FROM dbo.staging ST

INNER JOIN dbo.yearweek YK

ON ST.[Week] = YK.weekx

)

,GROUP_BOUNDRIES AS

(

SELECT

BD.STG_ID

,BD.SKU

,ROW_NUMBER() OVER

(

PARTITION BY BD.SKU,BD.WN_OFFSET

ORDER BY BD.rn ASC

) AS BD_WO_RID

,BD.WN_OFFSET

,BD.weekx

,BD.rn

FROM BASE_DATA BD

)

,GROUP_EDGES AS

(

SELECT

GB.STG_ID

,ROW_NUMBER() OVER

(

ORDER BY GB.STG_ID

) AS GB_RID

,GB.SKU

,GB.BD_WO_RID

,GB.WN_OFFSET

,GB.weekx

,GB.rn

FROM GROUP_BOUNDRIES GB

-- COMMENT OUT THE WHERE STATEMENT

-- TO RETRIEVE ALL RESULTS, THIS

-- ONLY SHOWS THE EDGES

WHERE GB.BD_WO_RID = 1

)

SELECT

GE1.STG_ID

,GE1.GB_RID

,GE1.SKU

,GE1.WN_OFFSET

,GE2.WN_OFFSET - GE1.WN_OFFSET

FROM GROUP_EDGES GE1

LEFT OUTER JOIN GROUP_EDGES GE2

ON GE1.GB_RID = GE2.GB_RID - 1

AND GE1.SKU = GE2.SKU;