[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

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!
0
rwaterz
Asked:
rwaterz
  • 3
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
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
 
PortletPaulCommented:
Thanks (and yes that answers my questions).
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now