So I have query to return data and a row number using ROW_NUMBER() OVER(PARTITION BY) and I place it into a temp table. The initial output looks the screenshot:
.
From here I need to, in the bt_newlabel column, replace the nulls respectively. So Rownumber 1-4 would be in progress, 5-9 would be underwriting, 10-13 would be implementation, and so forth.
I am hitting a wall trying to determine how to do this. Thanks for any help or input of how I would go about this.
One method is to assign groups, and then the value. Such as:
select t.*, max(bt_newlabel) over (partition by grp) as new_newlabel
from (select t.*, count(bt_newlabel) over (order by bt_stamp) as grp
from t
) t;
The group is simply the number of known values previously seen in the data.
You can update the field with:
with toupdate as (
select t.*, max(bt_newlabel) over (partition by grp) as new_newlabel
from (select t.*, count(bt_newlabel) over (order by bt_stamp) as grp
from t
) t
)
update toupdate
set bt_newlabel = new_newlabel
where bt_newlabel is null;
If I understood what you are trying to do, this is the type of update you need to do on your temp table:
--This will update rows 1-4 to 'Pre-Underwritting'
UPDATE temp_table SET bt_newlabel = 'Pre-Underwritting'
WHERE rownumber between
1 AND (SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Pre-Underwritting');
--This will update rows 5-9 to 'Underwritting'
UPDATE temp_table SET bt_newlabel = 'Underwritting'
WHERE rownumber between
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Pre-Underwritting')
AND
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Underwritting');
--This will update rows 10-13 to 'Implementation'
UPDATE temp_table SET bt_newlabel = 'Implementation'
WHERE rownumber between
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Underwritting')
AND
(SELECT TOP 1 rownumber FROM temp_table WHERE bt_oldlabel = 'Implementation');
I made a working Fiddle to check out the results: http://sqlfiddle.com/#!18/1cae2/1/3
