How to add/update a column with an incremented value and reset said value based on another column in SQL

Question

I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them. I tried rewriting the search term over and over but I guess I do not know exactly what to ask so I apologise if this has been answered already.

I am trying to figure out how I can add an INT column to a table which starts at and increments by 1 (let's call this field version) based on another columns value (which we shall call case_id) IF that value is the same as the previously iterated row - in the case it is NOT the same, then the version value should reset to 1.

I have tried to figure it out by either updating a temp table I create, or adding the value during the SELECT INTO statement.

The purpose of this table is to store a record of the historic updates to a case, identified by the case_id column, with this version value in the same order as the modify timestamp.

Consider this table structure as an example of the source data:

---------------------------------
| case_id | modify_time         |
---------------------------------
| 1       | 2015-05-01 11:12:13 |
| 1       | 2015-05-02 12:13:14 |
| 1       | 2015-05-03 13:14:15 |
| 2       | 2015-05-01 11:12:13 |
| 2       | 2015-05-01 12:13:14 |
| 3       | 2015-05-02 11:12:13 |
---------------------------------

Consider this as an example of exactly what I want to achieve as the end result:

-------------------------------------------
| case_id | modify_time         | version |
-------------------------------------------
| 1       | 2015-05-01 11:12:13 | 1       |
| 1       | 2015-05-02 12:13:14 | 2       |
| 1       | 2015-05-03 13:14:15 | 3       |
| 2       | 2015-05-01 11:12:13 | 1       |
| 2       | 2015-05-01 12:13:14 | 2       |
| 3       | 2015-05-02 11:12:13 | 1       |
-------------------------------------------

As you can see, I need to add the version (INT) column and insert an incremented value based on the modify_time (ascending order) which will reset when the case_id value changes.

I have tried a few things I have stumbled on during my searches, but nothing I have come across has been able to help me figure this out.

I guess this is just a little too advanced for me.. :-( This is on SQL Server 2012 and I am not afraid to try anything no matter how advanced as I can only learn from this exercise.

Thank you for taking the time to view my question and thank you in advance if you can help me out here. Cheers.

Answer 1

You can maintain this column easily:

;WITH x AS 
(
  SELECT case_id, modify_time, version, 
    rn = ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY modify_date)
  FROM dbo.source_table
)
UPDATE x SET version = rn WHERE version <> rn;

But since you could also generate that version column on the fly every time you need to query (maybe it could be in a view), I don't see any benefit of storing the output or even having a version column in the table, since you are potentially going to be updating it more often than you read it (you will have to update entire ranges or even the entire table every time any rows are changed or added).

If you are moving data from one system to another, or one table to another, you still don't need this column to exist in the source table, and you don't need a temporary table to store this column either:

INSERT dbo.target_table(case_id, modify_time, version)
SELECT case_id, modify_time, version = ROW_NUMBER() OVER
  (PARTITION BY case_id ORDER BY modify_date)
FROM dbo.source_table;