sqlsql-server

Add an incremental categorical column


I have a table with some production value and one of the column shows the type of product made. In my example below I am changing the type of product twice and I run the same product twice.

I would like to add a column that would help me compare the first run of prod-X with the second run of prod-X by adding a column named run and the value would be incremental run-1, run-2 and run-3.

How do I update this table to add those run index value to the row?

Thanks

Datetime            Width type                        Run
-----------------------------------------------------------
2024-01-01 06:00:00 1.45  prod-X --Change of product  run-1
2024-01-01 06:10:00 1.47  prod-X                      run-1
2024-01-01 08:00:00 2.56  prod-Y --Change of product  run-2
2024-01-01 08:10:00 2.67  prod-Y                      run-2
2024-01-02 07:00:00 1.87  prod-X --Change of product  run-3
2024-01-02 07:10:00 1.94  prod-X                      run-3

Solution

  • This is a classic gaps-and-islands problem where:

    create table MyTable([DateTime] datetime2(0), Width decimal(9,2), Type varchar(12));
    
    insert into MyTable([DateTime], Width, Type)
    values
    ('2024-01-01 06:00:00', 1.45, 'prod-X'),
    ('2024-01-01 06:10:00', 1.47, 'prod-X'),
    ('2024-01-01 08:00:00', 2.56, 'prod-Y'),
    ('2024-01-01 08:10:00', 2.67, 'prod-Y'),
    ('2024-01-02 07:00:00', 1.87, 'prod-X'),
    ('2024-01-02 07:10:00', 1.94, 'prod-X');
    
    with cte1 as (
      select *
        -- Detect a change in type when the previous row is different
        , case when type <> lag(type, 1, type) over (order by DateTime asc) then 1 else 0 end gp
      from MyTable
    ), cte2 as (
      select *
        -- Keep a running sum of the change in type to get unique groups
        , sum(gp) over (order by DateTime asc) + 1 groupnum
      from cte1
    )
    select [DateTime], Width, Type
      -- Build the required display label
      , 'run-' + convert(varchar(3), groupnum)
    from cte2
    order by [DateTime] asc;
    
    DateTime Width Type (No column name)
    2024-01-01 06:00:00 1.45 prod-X run-1
    2024-01-01 06:10:00 1.47 prod-X run-1
    2024-01-01 08:00:00 2.56 prod-Y run-2
    2024-01-01 08:10:00 2.67 prod-Y run-2
    2024-01-02 07:00:00 1.87 prod-X run-3
    2024-01-02 07:10:00 1.94 prod-X run-3

    db<>fiddle