Contiguous Order tsql

Question

I am looking for a contiguous algorithm in tsql. My data set up is like below.

IF OBJECT_ID('Tempdb..#Area_Version') IS NOT NULL 
DROP TABLE #Area_Version
CREATE TABLE #Area_Version
(
  AreaVersionId INT ,
  AreaId VARCHAR(1) ,
  VersionId VARCHAR(2) ,
  PocketGroupId INT
)

INSERT  INTO #Area_Version
    ( AreaVersionId, AreaId, VersionId, PocketGroupId )
VALUES  ( 1, 'A', 'V5', 0 ),
    ( 2, 'B', 'V2', 0 ),
    ( 3, 'C', 'V2', 0 ),
    ( 4, 'D', 'V3', 0 ),
    ( 5, 'E', 'V2', 0 ),
    ( 6, 'F', 'V1', 0 ),
    ( 7, 'F', 'V4', 0 ),
    ( 8, 'G', 'V3', 0 );   

Results:

AreaVersionId AreaId VersionId PocketGroupId
------------- ------ --------- -------------
1             A      V5        0
2             B      V2        0
3             C      V2        0
4             D      V3        0
5             E      V2        0
6             F      V1        0
7             F      V4        0
8             G      V3        0

(8 row(s) affected)

Below is a visual representation of the data.

AreaId  V1  V2  V3  V4  V5
A                        x
B            x            
C            x            
D                x        
E            x            
F        x           x  
G                x

Ordering by the AreaId, I am looking for contiguous groups. Since V2 is across B, C and E areas, V2 and V3 in D they can not be in one contiguous order. The data set above can result in two groups. (V5, V2, V1 and V3, V4) or (V5, V3, V4 and V2, V1). I can decide which set take precedence based on a quantity column which is out of scope for the question.

Based on the result set of the algorithm, I will update the PocketGroupId so that I can query based on the PocketGroupId and I will know that it is a contiguous group.

The desired result should be something like this.

AreaVersionId AreaId VersionId PocketGroupId
------------- ------ --------- -------------
1             A      V5        1
2             B      V2        1
3             C      V2        1
4             D      V3        2
5             E      V2        1
6             F      V1        1
7             F      V4        3
8             G      V3        2

Any ideas how this could be achieved. Thanks in advance.

Answer 1

IF OBJECT_ID('Tempdb..#Area_Version') IS NOT NULL 
    DROP TABLE #Area_Version
CREATE TABLE #Area_Version
    (
      AreaVersionId INT ,
      AreaId VARCHAR(1) ,
      VersionId VARCHAR(2) ,
      PocketGroupId INT
    )

INSERT  INTO #Area_Version
        ( AreaVersionId, AreaId, VersionId, PocketGroupId )
VALUES  ( 1, 'A', 'V5', 0 ),
        ( 2, 'B', 'V2', 0 ),
        ( 3, 'C', 'V2', 0 ),
        ( 4, 'D', 'V3', 0 ),
        ( 5, 'E', 'V2', 0 ),
        ( 6, 'F', 'V1', 0 ),
        ( 7, 'F', 'V4', 0 );
WITH    cte
          AS ( SELECT   AVLeft.AreaVersionId AS [LEFT] ,
                        AVLeft.AreaId ,
                        AVLeft.VersionId ,
                        AVLeft.PocketGroupId ,
                        COALESCE(MAX(AVRight.VersionId),
                                 ( SELECT   MIN(AV.VersionId)
                                   FROM     #Area_Version AS AV
                                 )) AS maxVersion ,
                        COALESCE(MIN(AVRight.VersionId),
                                 ( SELECT   MAX(AV.VersionId)
                                   FROM     #Area_Version AS AV
                                 )) AS minVersion
               FROM     #Area_Version AS AVLeft
                        LEFT JOIN #Area_Version AS AVRight ON AVLeft.AreaVersionId > AVRight.AreaVersionId
               GROUP BY AVLeft.AreaVersionId ,
                        AVLeft.AreaId ,
                        AVLeft.VersionId ,
                        AVLeft.PocketGroupId
             )
    SELECT  cte.[LEFT] ,
            cte.AreaId ,
            cte.VersionId ,
            CASE WHEN cte.VersionId > cte.minVersion
                      AND cte.VersionId < cte.maxVersion THEN 2
                 ELSE 1
            END AS PocketGroupId
    FROM    cte