Update record using previous updated field +1

Posted on 2014-08-11
Last Modified: 2014-08-12
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!
Question by:rwaterz
    LVL 47

    Expert Comment

    In your example

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


    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
    LVL 47

    Accepted Solution

          perf_code = new_perf_code
    FROM (
                    , '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
    FROM Table1

    Open in new window


    Author Comment

    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?
    LVL 47

    Expert Comment

    Thanks (and yes that answers my questions).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how the fundamental information of how to create a table.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now