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
This is a classic gaps-and-islands problem where:
LAG
SUM
over the number of changes so far e.g.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 |