Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Update record using previous updated field +1

Avatar of rwaterz
rwaterzFlag for United States of America asked on
Microsoft SQL Server
4 Comments1 Solution181 ViewsLast Modified:
I want to use a loop or cursor to update a record using the previous records info then adding 1. The record however is a varchar. The numbering of the record should increase by the date field. Example:

select top 3 * from T_PERF where prod_season_no = 8097 order by perf_dt

perf_no      prod_season_no      perf_code      perf_dt
8753      8097                        5P3-038*      1/31/2015
8752      8097                        5P3-037*      2/1/2015
8751      8097                        5P3-036*      2/1/2015

Desired result:
perf_no      prod_season_no      perf_code      perf_dt
8753      8097                        5P3-001       1/31/2015
8752      8097                        5P3-002       2/1/2015
8751      8097                        5P3-003             2/1/2015

The perf_code should increase by one from the previous perf_code in order of perf_dt asc. The good news is, we can treat the perf_code as  prefix & suffix. the prefix '5P3-' will not change, only the suffix '001'.

What would the syntax look like to achieve this? Thanks in advance!