Using SQL to Update a Counter Each Time a Field Value Changes (i.e increment count of records in a group)

I have a table that contains some data that has a count of the number times that same MATERIAL & PLANT combination appears. For example purposes, the table is called 'TABLE' and has the following fields and data.

MATERIAL, PLANT, COUNT_MAT_PLANT
10001, WE01, 1
10001, WE01, 2
10001, WE02, 1
10001, WE02, 2
10002, WE01, 1
10002, WE01, 2
10002, WE04, 1

I need some SQL that will update a new field called COUNT_MATERIAL_OCC with an incrementing number based upon how many times the MATERIAL appears in the data. I want the counter to increment based upon the sort order of data in this seuence MATERIAL, PLANT, COUNT_MAT_PLANT. The resulting output should look like this:

MATERIAL, PLANT, COUNT_MAT_PLANT, COUNT_MATERIAL
10001, WE01, 1, 1
10001, WE01, 2, 2
10001, WE02, 1, 3
10001, WE02, 2, 4
10002, WE01, 1, 1
10002, WE01, 2, 2
10002, WE04, 1, 3

Any help would be greatly appreciated.
thumbcastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
Solution for SQL Server 2005 is in the snippet.
;with cte as (
  select 
    METERIAL, 
    PLANT, 
    COUNT_MAT_PLANT, 
    ROW_NUMBER() OVER (PARTITION BY MATERIAL ORDER BY PLANT, COUNT_MAT_PLANT) AS rn
  from [TABLE]
)
update t
set t.COUNT_MATERIAL = c.rn
from [TABLE] t
inner join cte c
on t.MATERIAL = c.MATERIAL 
and t.PLANT = c.PLANT 
and t.COUNT_MAT_PLANT = c.COUNT_MAT_PLANT

Open in new window

0
thumbcastAuthor Commented:
Thanks for the assistance, however, I just tried creating a storer procedure with the code above, however, I get the follwoing errror

"ERROR 156: Incorrect Syntax near the keyword 'With'. ROW_NUMBER is not a recognized function name"
0
brejkCommented:
My solution was for SQL Server 2005. You are probably using some earlier version.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

thumbcastAuthor Commented:
I am using Microsoft  SQL Server 2000
0
Scott PletcherSenior DBACommented:
UPDATE t1
SET COUNT_MATERIAL = (
    SELECT COUNT(*)
    FROM tablename t2
    WHERE t2.MATERIAL = t1.MATERIAL
    AND (t2.PLANT < t1.PLANT OR
            (t2.PLANT = t1.PLANT AND t2.COUNT_MAT_PLANT <= t1.COUNT_MAT_PLANT))
    )
FROM tablename t1
0
Scott PletcherSenior DBACommented:
That could be a (very) slow, depending on the size of the table and the index(es) available.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thumbcastAuthor Commented:
Thank you Scott - that worked a treat. I only have a couple of thousand records to update, so the solution works perfectly.

Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.