SQL Server Performance

Generate Serie with Memory

Discussion in 'ALL SQL SERVER QUESTIONS' started by DannyArtavia, Apr 4, 2013.

  1. DannyArtavia New Member

    Hello, thanks for any help.
    Let's see... I need to modify a serie to generate a new one, but 'remembering' the new code for each previous repeted value.
    For example,
    I need a database from an Insurance company, but they don't want to give it e becuase it has a column with the ID number of each patient and their medical history.
    So, they will give it to me only if I can find a 'script' that allows them to modify that ID number, but I also need to 'remember' that ID across all the dase... Someone could have had an injury 10 times in a period of 15years... thats why I need the Script to generate a new ID serie but 'remembering' same ID's
  2. FrankKalis Moderator

    Welcome to the forum!
    Sorry, maybe I've missed something, but this company is willing to give you their database when you have a script that changes one meaningless ID number with another? Maybe you can describe what you're after by giving an example of what the data looks like now and what it should look like?
  3. DannyArtavia New Member

    Hi !!! thanks :).

    Exactly, they are willing to give it to me if I send a working script to them.

    Lets suppose this scenario:

    If I have (in a column) the ID 12345 and you 54321, y must generate a new number for each one. It can be 1 for me and 2 for you; but each time it reads 12345 it has to change to 1 exclusively.
    In conclusion, it has to identify each person with a new ID number.

    OF course, the table have additional columns...

    Thank u very much for any assistance.
  4. davidfarr Member

    --Create some test numbers in a table, include some duplicates;

    CREATE TABLE #temp1 (ID_number varchar(20))
    insert #temp1 values ('12345')
    insert #temp1 values ('12345')
    insert #temp1 values ('54321')
    insert #temp1 values ('54321')
    insert #temp1 values ('97865')
    insert #temp1 values ('54566')
    insert #temp1 values ('13467')
    insert #temp1 values ('93476')

    --Query to generate new ID's, unique per ID number;

    SELECT *,RANK() OVER (ORDER BY ID_number) as 'New_ID' from #temp1

Share This Page