The idea of the "Set First" and "Set Last" columns is that they are the first and last records in each set.

Once you have those, what you need to do is select the ones where SetFirst = 1, and assign a row_number to them, and do the same with the SetEnd = 1, then join those together and get everything in between them.

Something like this (adding a "SetID" column, data type = int, and "SetSequence", also int):

;with

Sets1 (ID, SetID1) as

(select id, row_number() over (partition by prd order by date)

from #TempTable

where SetFirst = 1),

Sets2 (ID, SetID2) as

(select id, row_number() over (partition by prd order by date)

from #TempTable

where SetLast = 1)

update TempTable

set SetID = SetID1

from Sets1

inner join Sets2

on Sets1.SetID1 = Sets2.SetID2

inner join #TempTable TempTable

on TempTable.ID between SetID1 and SetID2;

with

SetSeq (ID, SetSequence) as

(select ID,

row_number() over (partition by SetID order by date)

from #TempTable)

update TempTable

set SetSequence = SetSeq.SetSequence

from #TempTable TempTable

inner join SetSeq

on TempTable.ID = SetSeq.ID

The first query breaks them up into sets, based on the SetStart and SetEnd columns and the Date column.

The second query then takes each set and gives it a sequence number within the set.

Is that what you're looking for? Is it clear enough?