Looks like it's time for a quirky update solution:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
-- This primary key is crucial. If your table doesn't have a clustered index
-- on this, then you will have to dump the data to a temp table (along with
-- the PK columns of the table), use this PK, then update the real table
-- by joining this temp table back to the real table by the PK columns.
CREATE TABLE #temp (ckid INT PRIMARY KEY CLUSTERED, stat varchar(10), Grp int);
INSERT INTO #temp (ckid, stat)
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open';
-- declare and initialize variables needed in the update statement.
DECLARE @Sequence int, -- for safety check
@stat varchar(10), -- to hold stat column from last row
@grp int, -- current grp number
@ckid int; -- for anchor column
SET @Sequence = 1;
SET @grp = 1;
/*
This form of the UPDATE statement has some rules for proper usage.
See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
for a complete discussion of how this works, and all of the rules for utilizing it.
If you don't follow ALL the rules, you WILL mess up your data.
*/
WITH SafeTable AS
(
-- generate table with a sequence column in clustered index order
-- in order to verify that update is happening in the correct order
SELECT ckid,
stat,
grp,
Sequence = ROW_NUMBER() OVER (ORDER BY ckid)
FROM #temp
)
UPDATE t
-- verify in proper sequence order; if not, throw an error so nothing is updated
SET @grp = grp = CASE WHEN Sequence = @Sequence THEN
CASE WHEN stat <> @Stat THEN @grp + 1
-- if you have to separate this by account numbers also, then you will need
-- a when clause to handle it here also.
-- different stat --> increment grp number
ELSE @grp END -- same stat --> same grp number
ELSE 1/0 END, -- not in proper sequence order, so throw an error
@Sequence = @Sequence + 1,
@stat = stat, -- get the current value to compare to in next row
@ckid = ckid -- anchor column
FROM SafeTable t WITH (TABLOCKX) -- lock table
OPTION (MAXDOP 1); -- prevent parallelism!
select * from #temp;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes