Post reply

Categorize data without an indicator in the table

  • April 30, 2019 at 6:57 pm

    #3567678

    I have a table that has two columns as follows. I am not able to categorize colB by using windows function ( tried rank, dense rank, tile, row number)to get the expected output. Whenever I use these functions, I end up including the last 725 records with the other top 725. At-least, I should be able to detect the change in value in colB so that I can use some aggregate function with a group by.

    Can someone provide their expert opinion? I am using Microsoft APS 2016.

    ColBColA
    7257
    7258
    7259
    72510
    72511
    72512
    72513
    145914
    145915
    145916
    145917
    145918
    145919
    145920
    72521

    Expected Result
    minColAmaxColA
    725713
    14591420
    7252121

  • April 30, 2019 at 7:27 pm

    #3568613

    This is a standard gaps and islands problem, but it can also be solved with LEAD()/LAG().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • April 30, 2019 at 9:31 pm

    #3575820

    If you provide data in the consumable, ready to run format, you would usually get more responses.

    Try this:

    CREATE TABLE #Temp(ColB INT, ColA INT);
    INSERT INTO #Temp (ColB,ColA)
      VALUES (725,7),(725,8),(725,9),(725,13),
             (1459,14),(1459,15),
             (725,21),(725,23),
             (1459,32),(1459,35);

    WITH GrpCTE AS (
      SELECT *, 
        ROW_NUMBER() OVER (ORDER BY ColA) - ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA) AS Grp
      FROM #Temp
    )
    SELECT 
      ColB, 
      MIN(GrpCTE.ColA) AS minColA,
      MAX(GrpCTE.ColA) AS maxColA
    FROM GrpCTE
    GROUP BY GrpCTE.ColB,GrpCTE.Grp
    ORDER BY minColA

    DROP TABLE #Temp

    --Vadim R.

  • May 1, 2019 at 3:44 am

    #3592639

    Thank you rVadim .The solution works

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply