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

Answer 2

Here is a dynamic version using a cursor and while loop... and extra temp tables. It's gross but works.

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)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (2, 'B', 'V2', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (3, 'C', 'V2', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (4, 'D', 'V3', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (5, 'E', 'V2', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (6, 'F', 'V1', 0)
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (7, 'F', 'V4', 0)    
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (8, 'G', 'V2', 0)    
INSERT INTO #Area_Version (AreaVersionId, AreaId, VersionId, PocketGroupId) VALUES (9, 'H', 'V4', 0)    

IF OBJECT_ID('Tempdb..#WorkingArea_Version') IS NOT NULL
    DROP TABLE #WorkingArea_Version

CREATE TABLE #WorkingArea_Version
    (
      AreaVersionId INT ,
      AreaId VARCHAR(1) ,
      VersionId VARCHAR(2) ,
      PocketGroupId INT
    )

INSERT INTO #WorkingArea_Version
SELECT * FROM #Area_Version

IF OBJECT_ID('Tempdb..#resultset') IS NOT NULL
    DROP TABLE #resultset

CREATE TABLE #resultset
    (
      PocketGroupId INT ,
      VersionId NVARCHAR(MAX)
    )

DECLARE @areaCount INT 
SELECT  @areaCount = COUNT(1)
FROM    #WorkingArea_Version av

 DECLARE @PocketGroupId INT
SET @PocketGroupId = 1

WHILE ( @areaCount > 0 )
    BEGIN

        DECLARE @AreaVersionId INT ,
            @AreaId VARCHAR(1) ,
            @VersionId VARCHAR(2)

        DECLARE db_cursor CURSOR
        FOR
            SELECT  av.AreaVersionId ,
                    av.AreaId ,
                    av.VersionId
            FROM    #WorkingArea_Version av
            ORDER BY av.AreaId

        OPEN db_cursor   
        FETCH NEXT FROM db_cursor INTO @AreaVersionId, @AreaId, @VersionId


        WHILE @@FETCH_STATUS = 0
            BEGIN   

                DECLARE @Versions NVARCHAR(MAX) = ''
                IF NOT EXISTS ( SELECT TOP 1
                                        1
                                FROM    #resultset r
                                WHERE   r.PocketGroupId = @PocketGroupId )
                    BEGIN

                        INSERT  INTO #resultset
                                ( PocketGroupId, VersionId )
                        VALUES  ( @PocketGroupId, @VersionId )                
                    END
                ELSE
                    BEGIN

                        DECLARE @minAreaId NVARCHAR(1)
                        DECLARE @maxAreaId NVARCHAR(1)

            --current max
                        SELECT  @maxAreaId = MAX(AreaId)
                        FROM    #resultset r
                                JOIN #WorkingArea_Version av ON av.VersionId = r.VersionId

            --potentials min
                        SELECT  @minAreaId = MIN(AreaId)
                        FROM    #WorkingArea_Version av
                        WHERE   av.VersionId = @VersionId                           

                        IF ( @minAreaId > @maxAreaId )
                            BEGIN
                                INSERT  INTO #resultset
                                        ( PocketGroupId, VersionId )
                                VALUES  ( @PocketGroupId, -- PocketGroupId - int
                                          @VersionId  -- VersionId - nvarchar(max)
                                          )
                            END     
                    END

                FETCH NEXT FROM db_cursor INTO @AreaVersionId, @AreaId,
                    @VersionId
            END
        CLOSE db_cursor   
        DEALLOCATE db_cursor

        DELETE  FROM #WorkingArea_Version
        WHERE   VersionId IN ( SELECT   r.VersionId
                               FROM     #resultset r )

        SELECT  @areaCount = COUNT(1)
        FROM    #WorkingArea_Version av
        SET @PocketGroupId = @PocketGroupId + 1
    END



    SELECT av.AreaVersionId, av.AreaId, av.VersionId, r.PocketGroupId
    FROM #Area_Version av
    JOIN #resultset r ON r.VersionId = av.VersionId
    ORDER BY av.AreaVersionId