Creating blocks within a CTE - SQL Server

Question

I am trying to work out how I can tag unique (what i am calling) blocks (or segments if you will) which have a start and end based consecutive 'Trip' rows ordered by 'epoch' sharing the same 'code'. In this case group by 'trip', 'code' will not work as I need to measure the duration of the 'code' remains constant for the trip. I've tried to use a CTE but I have been unable to partition the data in such a way that it gives desired result shown below. The block number I've shown could be any value, just so long as it is unique so that it tags the consecutive occurrences of the same 'code' on the trip in order of 'epoch'.

Any ideas?

declare @data table (id int, trip int, code int NULL, epoch int, value1 int, value2 int);
insert into @data (id, trip, code, epoch, value1, value2)
values 
(1, 1, null, 31631613, 0, 0),
(2, 2, 1, 31631614, 10, 40),
(3, 1, 1, 31631616, 10, 60),
(4, 1, 1, 31631617, 40, 60),
(5, 2, 1, 31631617, 23, 40),
(6, 2, 2, 31631620, 27, 40),
(7, 2, 2, 31631629, 23, 40),
(9, 1, 1, 31631618, 39, 60),
(10, 1, null, 31631621, 38, 60),
(12, 1, null, 31631625, 37, 60),
(15, 1, null, 31631627, 35, 60),
(19, 1, 1, 31631630, 39, 60),
(20, 1, 1, 31631632, 40, 60),
(21, 2, 1, 31631629, 23, 40);


block   id  trip    code        epoch   value1  value2
1       1   1       NULL    31631613    0   0
2       2   2       1       31631614    10  40
2       5   2       1       31631617    23  40
3       3   1       1       31631616    10  60
3       4   1       1       31631617    40  60
3       9   1       1       31631618    39  60
4       6   2       2       31631620    27  40
4       7   2       2       31631629    23  40
5       10  1       NULL    31631621    38  60
5       12  1       NULL    31631625    37  60
5       15  1       NULL    31631627    35  60
6       19  1       1       31631630    39  60
6       20  1       1       31631632    40  60
7       21  2       1       31631629    23  40

Answer 1

Ok, it's far from perfect by any means but it is a starter that at least identifies the start and end of a contiguous block where the 'code' has remained the same for the trip. For the sake of at least contributing something I'll post what I jerried up. If I ever get time to do a proper job I'll post it.

declare @minint int; set @minint = -2147483648;
declare @maxint int; set @maxint = 2147483647;
declare @id_data table (pk int IDENTITY(1,1), id int, trip int, code int NULL, epoch int, value1 int, value2 int);

insert into @id_data VALUES(@minint, @minint, @minint, @minint, @minint, @minint);
insert into @id_data
SELECT id, trip, coalesce(code,0), epoch, value1, value2
  FROM @data
order by trip, epoch, code;
insert into @id_data VALUES(@maxint, @maxint, @maxint, @maxint, @maxint, @maxint);

WITH CTE as 
(
SELECT pk, id, trip, code, epoch, value1, value2, ROW_NUMBER() OVER (PARTITION BY trip ORDER BY epoch) as row_num
FROM @id_data
)
SELECT B.*, A.code, C.min_next_code 
FROM CTE A
INNER JOIN CTE B ON (B.pk = A.pk + 1) AND (A.code != B.code) -- SELECTS THE RECORDS     THAT START A NEW GROUP
OUTER APPLY (
SELECT min_next_code = MIN(pk) - 1 -- LOCATION OF NEXT GROUP
FROM CTE
WHERE pk > B.pk AND (trip = B.trip) AND (code != B.code)
) C
WHERE B.id < @maxint

Answer 2

You didn't update your expected output so I'm still not 100% sure this is what you want, but give it a try...

SELECT 
    DENSE_RANK() OVER (ORDER BY trip, code), 
    * 
FROM 
    @data
ORDER BY 
    trip, code, epoch