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.
ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY modify_time)
, I fail to see the benefit of storing redundant data (and having to maintain it, potentially touching all rows, any time any single row is changed or added).modify_time
especially. I would suggest you to look in to this sqlfiddle example. sqlfiddle.com/#!6/e2c8a