Insert Ctr Col That Resets based on prior row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert Ctr Col That Resets based on prior row

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2009-09-01 : 16:24:14

I have a table that currently has an identity column that increments by one and a value column that has values between 0 and 7. I would like to insert a third column that is a counter that resets anytime the value column is 7 otherwise it increases by one.

Sample Table:

CREATE TABLE #valueset (k1 int identity, c1 int)
INSERT #valueset (c1) VALUES (7)
INSERT #valueset (c1) VALUES (0)
INSERT #valueset (c1) VALUES (3)
INSERT #valueset (c1) VALUES (4)
INSERT #valueset (c1) VALUES (7)
INSERT #valueset (c1) VALUES (0)
INSERT #valueset (c1) VALUES (7)
INSERT #valueset (c1) VALUES (1)
INSERT #valueset (c1) VALUES (5)

SELECT * FROM #valueset
Drop table #valueset

Results:
1 7
2 0
3 3
4 4
5 7
6 0
7 7
8 1
9 5

I would like the results to look like this:

1 7 1
2 0 2
3 5 3
4 3 4
5 7 1
6 0 2
7 7 1
8 1 2
9 5 3

Any help is greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-01 : 17:56:00

What will this new column be used for? This type of requirement is usually not a good design.

but...
Here is one way you can do it
NOTE: that with this method the clustered index and an anchor variable is necessary to insure the order the updates take place



CREATE TABLE #valueset (k1 int identity, c1 int)
set nocount on
INSERT #valueset (c1) VALUES (7)
INSERT #valueset (c1) VALUES (0)
INSERT #valueset (c1) VALUES (3)
INSERT #valueset (c1) VALUES (4)
INSERT #valueset (c1) VALUES (7)
INSERT #valueset (c1) VALUES (0)
INSERT #valueset (c1) VALUES (7)
INSERT #valueset (c1) VALUES (1)
INSERT #valueset (c1) VALUES (5)

go
alter table #valueset add primary key clustered (k1)
alter table #valueSet add newCol int null
go
declare @nc int, @dummyAnchor int
set @nc = 1

update #valueSet set
       @nc = newCol = case when c1 = 7 then 1 else @nc + 1 end
       ,@dummyAnchor = k1 --according to Jeff Moden, besides the clustered index, this is also necessary to guarantee the order of the updates

select * from #valueset
       

go
drop table #valueset

output:

k1          c1          newCol
----------- ----------- -----------
1           7           1
2           0           2
3           3           3
4           4           4
5           7           1
6           0           2
7           7           1
8           1           2
9           5           3

Be One with the Optimizer
TG

legacyvbc
Starting Member

37 Posts

Posted - 2009-09-01 : 18:36:31

Thanks for the help. This is just a stop gap until I have a chance to rewrite some of the processing code to reflect some knew information that I want to add to the reports.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources