1

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.

  • 1
    Why do you need to update the table with this value? Since this is something you can always calculate at runtime (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). – Aaron Bertrand Sep 14 '15 at 16:03
  • @metcaelfe Your output is not correct modify_time especially. I would suggest you to look in to this sqlfiddle example. sqlfiddle.com/#!6/e2c8a – Kin Shah Sep 14 '15 at 16:05
  • ok, so even though I did look at this answer: link it did not occur to me that this may lead me to a solution... I think however that in T-SQL it would be the OVER clause.. – metcaelfe Sep 14 '15 at 16:07
  • @AaronBertrand - truth be told, I am preparing data from one case management tool DB and inserting into another tools' DB, so this is a part of building the required tables for a direct insertion because that is all I know how to do :-( – metcaelfe Sep 14 '15 at 16:13
  • I agree with Aaron's solution that you don't need to store this additional field. It can be created dynamically at runtime. You can refer to my sql fiddle or Aaron's solution which is essentially the same :-) – Kin Shah Sep 14 '15 at 16:14
4

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;
  • @AaronBetrand - Thank you so much for this example! it has provided me with the solution, using essentially a direct copy/paste of your code and the correct fields of course. THANK YOU! Admittedly I have used it in a primitive manner and it is simply setting the values in my temp table from which I will be inserting the data. But I will take on board these suggestions from yourself and Kin so that in the future I might do this more efficiently and perhaps with a little more elegance :-D Cheers! – metcaelfe Sep 14 '15 at 16:22
  • @AaronBetrand - PS: I saw your additional content after posting my gratitude and marking as answer, but it is no less significant as it is yet another example of achieving the same result in a very succinct manner! I cannot say thank you enough so to save us all the repetition here it is one last time: Thank You sir! – metcaelfe Sep 14 '15 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.