Update record using previous updated field +1

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

Open in new window


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!
rwaterzAsked:
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.

PortletPaulfreelancerCommented:
In your example

8752 8097 2/1/2015 | 5P3-037* -->>becomes >>  5P3-002

but

8751 8097 2/1/2015 | 5P3-036* -->>becomes >>  5P3-003

2/1/2015 = 2/1/2015
8751 < 8752
5P3-036 < 5P3-037

why is this? why wouldn't this be acceptable?

perf_no prod_season_no      perf_code      perf_dt
8753      8097                        5P3-001       01/31/2015
8751      8097                        5P3-002       02/1/2015
8752      8097                        5P3-003       02/1/2015
0
PortletPaulfreelancerCommented:
UPDATE
      Table1
SET
      perf_code = new_perf_code
FROM (
            SELECT
                  perf_no
                , '5P3-'
                  + RIGHT('000'
                  + CAST(
                              ROW_NUMBER() OVER (PARTITION BY prod_season_no
                                                 ORDER BY perf_dt ASC, perf_no DESC)
                        AS varchar)
                  , 3)                                AS new_perf_code
            FROM table1
      ) sq
WHERE table1.perf_no = sq.perf_no
;

SELECT
*
FROM Table1

Open in new window

see http://sqlfiddle.com/#!3/e93cb/6
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
rwaterzAuthor Commented:
Thank Paul. I hope this answers your question; The perf_no won't always necessarily be aligned with the perf date for various reasons (such as we decided to add a performance of an earlier date after the original set of perf's were created).

Also, when I carried over the perf_dt from SSMS to Excel, it didn't format the time, just the date. So the perf_dt should be (taking form the 'desired result'):

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

Does that answer your question?
0
PortletPaulfreelancerCommented:
Thanks (and yes that answers my questions).
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.