Make records unique in table using time field

  • I have this in my table:

    Table: detail_data

    person_id, person_number, date, item, qty, visit_id, visit_number

    1, A12, 1/1/08, AA, 5, 10, B11

    1, A12, 1/1/08, AA, 1, 10, B11

    1, A12, 1/1/08, AA, 3, 10, B11

    The table does not have a primary key but I will need to add one with the following fields:

    person_id, date, item, visit_id

    If I attempt to add this now I will get a PK error as there are duplicates.

    So I want to add a new field called time and populate this with a time starting at 12:00:00am and then increment a second to the time each time there is a duplicate record.

    The table would then look like this:

    Table: detail_data

    person_id, person_number, date, time, item, qty, visit_id, visit_number

    1, A12, 1/1/08, 12:00:00, AA, 5, 10, B11

    1, A12, 1/1/08, 12:00:01, AA, 1, 10, B11

    1, A12, 1/1/08, 12:00:02, AA, 3, 10, B11

    Any ideas?

  • If I'm understanding you correctly, the best way would by to add an ID field and enable the identity property on it. This will automatically increment a unique number in this field whenever a new record is entered.

    Strick

  • I'd go with the identity value or GUID. Not time.

  • Hi Strick

    Thanks for the reply. Unfortunately I am restricted to the fields currently defined in the table, I cannot add another field like the indentity. The time field does exist but is currently populated with nulls, and as I cannot change any of the other fields (as these are populated already with valid data), and this one is part of the key, it is the only one I can play with.

    I plan on doing something like this

    declare @update_time as smalldatetime

    select @update_time = '12:00:00'

    UPDATE detail_data

    SET time = DATEADD('ss', 1, @update_time)

    But I am unsure how to have it run through a loop for each person so that a second is incremented to each duplicate.

    Thanks

  • Hi,

    I see,

    Do you have the ability to change the default propert in your time field? You can set the default propert to (getdate()) which will timestamp this field as soon as a new record touches the table. That way you wont have to do anything extra to make your time unique. Time is always unique.

    Strick

  • It is a not a problem with new records being added - the load program has since been changed to accomodate that. It is only a problem with the records currently residing in the table that need the unique times updated, so we can move forward.

    Thanks

  • Bounce your records into a temporary table with an identity field on it.

    Then do something similar to the pseudo query below

    UPDATE DEST

    SET DateTime = DATEADD(mm,T.ID-DT.FirstID,GETDATE())

    FROM dbo.YourTable AS YT

    INNER JOIN #TempTable AS T ON

    YT. -- You will have to repeat this for all fields.

    INNER JOIN (

    SELECT <FieldList you want to be unique),MIN(ID AS FirstID)

    FROM #TempTable

    GROUP BY <FieldList you want to be unique)

    ) AS DT

    ON T.

  • David,

    I tried this query

    UPDATE detail_data1

    SET time = DATEADD(ss,T.ID-DT.FirstID,GETDATE())

    FROM detail_data1 AS YT

    INNER JOIN detail_data1 AS T ON YT

    INNER JOIN (

    SELECT person_id, date, time, item, visit_id

    FROM detail_data1

    GROUP BY person_id, date, time, item, visit_id

    ) AS DT

    ON T.

    and received this error:

    Server: Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'INNER'.

    Server: Msg 156, Level 15, State 1, Line 9

    Incorrect syntax near the keyword 'AS'.

    What is wrong?

    Thanks

  • rolli (2/2/2008)


    David,

    I tried this query

    UPDATE detail_data1

    SET time = DATEADD(ss,T.ID-DT.FirstID,GETDATE())

    FROM detail_data1 AS YT

    INNER JOIN detail_data1 AS T ON YT

    INNER JOIN (

    SELECT person_id, date, time, item, visit_id

    FROM detail_data1

    GROUP BY person_id, date, time, item, visit_id

    ) AS DT

    ON T.

    and received this error:

    Server: Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'INNER'.

    Server: Msg 156, Level 15, State 1, Line 9

    Incorrect syntax near the keyword 'AS'.

    What is wrong?

    Thanks

    You're missing pieces to two parts of the statement.

    UPDATE detail_data1

    SET time = DATEADD(ss,T.ID-DT.FirstID,GETDATE())

    FROM detail_data1 AS YT

    INNER JOIN detail_data1 AS T

    ON YT --<<<<<missing relation info, should be like "ON yt.id=t.id"

    INNER JOIN (

    SELECT person_id, date, time, item, visit_id

    FROM detail_data1

    GROUP BY person_id, date, time, item, visit_id

    ) AS DT

    ON T --<<<<<missing relation info, should be like "ON t.id=dt.id"

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • This is a SQL Server 2005 forum, right?

    To test, use this

    -- Prepare sample data

    DECLARE@Sample TABLE (Person_ID TINYINT, Person_Number CHAR(3), Date DATETIME, Item CHAR(2), Qty TINYINT, Visit_ID TINYINT, Visit_Number CHAR(3))

    SET DATEFORMAT MDY

    INSERT@Sample

    SELECT1, 'A12', '1/1/08', 'AA', 5, 10, 'B11' UNION ALL

    SELECT1, 'A12', '1/1/08', 'AA', 1, 10, 'B11' UNION ALL

    SELECT1, 'A12', '1/1/08', 'AA', 3, 10, 'B11'

    -- Show original data

    SELECT*

    FROM@Sample

    -- Do the necessary update

    UPDATEf

    SETf.Date = DATEADD(SECOND, f.RecID, f.Date)

    FROM(

    SELECTDate,

    ROW_NUMBER() OVER (PARTITION BY Person_ID, Date, Item, Visit_ID ORDER BY Qty) - 1 AS RecID

    FROM@Sample

    ) AS f

    -- Show updated data

    SELECT*

    FROM@Sample

    And for your environment, use

    UPDATEf

    SETf.Date = DATEADD(SECOND, f.RecID, f.Date)

    FROM(

    SELECTDate,

    ROW_NUMBER() OVER (PARTITION BY Person_ID, Date, Item, Visit_ID ORDER BY Qty) - 1 AS RecID

    FROMDetail_Data

    ) AS f


    N 56°04'39.16"
    E 12°55'05.25"

  • stricknyn (2/1/2008)


    You can set the default propert to (getdate()) which will timestamp this field as soon as a new record touches the table. That way you wont have to do anything extra to make your time unique. Time is always unique.

    I have to disagree

    -- Prepare sample data

    DECLARE@Sample TABLE

    (

    MyColumn SMALLINT PRIMARY KEY CLUSTERED,

    MyTime DATETIME DEFAULT (GETDATE())

    )

    -- Populate sample table

    INSERT@Sample

    (

    MyColumn

    )

    SELECTNumber

    FROMmaster..spt_values

    WHEREType = 'p'

    -- Show sample data

    SELECT*

    FROM@Sample


    N 56°04'39.16"
    E 12°55'05.25"

  • Peso, you beat me to the punch on the ROW_NUMBER suggestion 🙂

    If you're stuck with SQL 2000 then moving the data to a temp table with an identity and then moving it back is the best way to go.

    However, using getDate() as the default isn't so bad. All of the inserted values will get the same date. Unless you're expecting duplicates within a single insert statement's data then you should be fine. In fact, it may help enforce uniqueness in the future 🙂

  • I agree that the getdate() between single selects almost always are unique, but however you can't rely on that to 100%.

    The getdate() function works very much like RAND(). It seeds when the statemen is executed and retains it's value through whole execution for that statement.


    N 56°04'39.16"
    E 12°55'05.25"

  • Be very careful even with datetime for uniqueness. I believe the smallest time increment is 3ms. We've had this problem in the past several times. Even datetime keys can cause duplicates.

  • Be very careful with datetime for uniqueness. I believe the smallest time increment is 3ms. We've had this problem in the past several times. Even datetime keys can cause duplicates.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply