Add one min when time same

  • Hi guys,

    I have a table which has below records. I have to add 1 min when time comes in second records.

    Records are as belows:

    1900-01-01 12:00:00.000

    1900-01-01 12:15:00.000

    1900-01-01 12:30:00.000

    1900-01-01 12:45:00.000

    1900-01-01 13:00:00.000

    1900-01-01 13:15:00.000

    1900-01-01 13:30:00.000

    1900-01-01 13:45:00.000

    1900-01-01 14:00:00.000

    1900-01-01 14:15:00.000

    1900-01-01 14:30:00.000

    1900-01-01 14:30:00.000

    1900-01-01 14:45:00.000

    1900-01-01 15:00:00.000

    1900-01-01 15:15:00.000

    1900-01-01 15:30:00.000

    1900-01-01 15:45:00.000

    1900-01-01 16:00:00.000

    1900-01-01 16:15:00.000

    1900-01-01 16:30:00.000

    1900-01-01 16:45:00.000

    1900-01-01 17:00:00.000

    1900-01-01 17:15:00.000

    1900-01-01 17:30:00.000

    1900-01-01 17:45:00.000

    1900-01-01 18:00:00.000

    1900-01-01 18:15:00.000

    1900-01-01 18:30:00.000

    1900-01-01 18:45:00.000

    1900-01-01 19:00:00.000

    1900-01-01 19:15:00.000

    1900-01-01 19:30:00.000

    1900-01-01 19:45:00.000

    1900-01-01 20:00:00.000

    1900-01-01 20:15:00.000

    1900-01-01 20:30:00.000

    1900-01-01 20:45:00.000

    1900-01-01 21:00:00.000

    1900-01-01 21:15:00.000

    1900-01-01 21:30:00.000

    1900-01-01 21:45:00.000

    1900-01-01 22:00:00.000

    1900-01-01 22:15:00.000

    1900-01-01 22:30:00.000

    1900-01-01 22:45:00.000

    1900-01-01 23:00:00.000

    In the above rows, 1900-01-01 14:30:00.000 comes twice. So I have to add one miniute after 1900-01-01 14:30:00.000 row. So result will come like belows:

    1900-01-01 12:00:00.000

    1900-01-01 12:15:00.000

    1900-01-01 12:30:00.000

    1900-01-01 12:45:00.000

    1900-01-01 13:00:00.000

    1900-01-01 13:15:00.000

    1900-01-01 13:30:00.000

    1900-01-01 13:45:00.000

    1900-01-01 14:00:00.000

    1900-01-01 14:15:00.000

    1900-01-01 14:30:00.000

    1900-01-01 14:31:00.000

    1900-01-01 14:45:00.000

    1900-01-01 15:00:00.000

    1900-01-01 15:15:00.000

    1900-01-01 15:30:00.000

    1900-01-01 15:45:00.000

    1900-01-01 16:00:00.000

    1900-01-01 16:15:00.000

    1900-01-01 16:30:00.000

    1900-01-01 16:45:00.000

    1900-01-01 17:00:00.000

    1900-01-01 17:15:00.000

    1900-01-01 17:30:00.000

    1900-01-01 17:45:00.000

    1900-01-01 18:00:00.000

    1900-01-01 18:15:00.000

    1900-01-01 18:30:00.000

    1900-01-01 18:45:00.000

    1900-01-01 19:00:00.000

    1900-01-01 19:15:00.000

    1900-01-01 19:30:00.000

    1900-01-01 19:45:00.000

    1900-01-01 20:00:00.000

    1900-01-01 20:15:00.000

    1900-01-01 20:30:00.000

    1900-01-01 20:45:00.000

    1900-01-01 21:00:00.000

    1900-01-01 21:15:00.000

    1900-01-01 21:30:00.000

    1900-01-01 21:45:00.000

    1900-01-01 22:00:00.000

    1900-01-01 22:15:00.000

    1900-01-01 22:30:00.000

    1900-01-01 22:45:00.000

    1900-01-01 23:00:00.000

    Thanks in advance.

  • And what if the modified time (+1 min) is duplicate as well?

    -- Gianluca Sartori

  • edited ... removed the quoted text :blush:

    Can you use Row_Number() over ( partition by yourdatecol order by yourdatecol ) as RwNumber

    and add that ( - 1)

    e.g.

    update x

    set yourdatecol = DATEADD(mi, RwNumber- 1, yourdatecol )

    from (

    select top 5

    *

    , ROW_NUMBER() over ( partition by yourdatecol order by yourdatecol ) RwNumber

    from dbo.yourtable

    ) x

    TEST it ---- TEST IT

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi,

    I have slightly modified the query and it worked for me.

    So query is:

    UPDATE t1 SET sessionStTime = DATEADD(mi, RwNumber-1, sessionStTime)

    FROM

    (

    SELECT sessionStTime,ROW_NUMBER() OVER (PARTITION by sessionStTime ORDER BY sessionStTime) RwNumber

    FROM @Sessions

    ) t1

    Thank you so much.. 😉

  • ALZDBA (9/16/2011)


    edited ... removed the quoted text :blush:

    Can you use Row_Number() over ( partition by yourdatecol order by yourdatecol ) as RwNumber

    and add that ( - 1)

    e.g.

    update x

    set yourdatecol = DATEADD(mi, RwNumber- 1, yourdatecol )

    from (

    select top 5

    *

    , ROW_NUMBER() over ( partition by yourdatecol order by yourdatecol ) RwNumber

    from dbo.yourtable

    ) x

    TEST it ---- TEST IT

    Yes Johan, that's what I though from the start but it doesn't deal with "new" duplicates.

    Try testing it against this sample data:

    WITH sampleData1(yourcharcol) AS (

    SELECT '1900-01-01 12:00:00.000'

    UNION ALL SELECT '1900-01-01 12:15:00.000'

    UNION ALL SELECT '1900-01-01 12:30:00.000'

    UNION ALL SELECT '1900-01-01 12:45:00.000'

    UNION ALL SELECT '1900-01-01 13:00:00.000'

    UNION ALL SELECT '1900-01-01 13:15:00.000'

    UNION ALL SELECT '1900-01-01 13:30:00.000'

    UNION ALL SELECT '1900-01-01 13:45:00.000'

    UNION ALL SELECT '1900-01-01 14:00:00.000'

    UNION ALL SELECT '1900-01-01 14:15:00.000'

    UNION ALL SELECT '1900-01-01 14:30:00.000' -- FIRST PASS DUPLICATE, RowNumber = 1

    UNION ALL SELECT '1900-01-01 14:30:00.000' -- FIRST PASS DUPLICATE, RowNumber = 2

    UNION ALL SELECT '1900-01-01 14:30:00.000' -- FIRST PASS DUPLICATE, RowNumber = 3

    UNION ALL SELECT '1900-01-01 14:31:00.000' -- SECOND PASS DUPLICATE

    UNION ALL SELECT '1900-01-01 14:32:00.000' -- SECOND PASS DUPLICATE

    UNION ALL SELECT '1900-01-01 15:15:00.000'

    UNION ALL SELECT '1900-01-01 15:30:00.000'

    UNION ALL SELECT '1900-01-01 15:45:00.000'

    UNION ALL SELECT '1900-01-01 16:00:00.000'

    UNION ALL SELECT '1900-01-01 16:15:00.000'

    UNION ALL SELECT '1900-01-01 16:30:00.000'

    UNION ALL SELECT '1900-01-01 16:45:00.000'

    UNION ALL SELECT '1900-01-01 17:00:00.000'

    UNION ALL SELECT '1900-01-01 17:15:00.000'

    UNION ALL SELECT '1900-01-01 17:30:00.000'

    UNION ALL SELECT '1900-01-01 17:45:00.000'

    UNION ALL SELECT '1900-01-01 18:00:00.000'

    UNION ALL SELECT '1900-01-01 18:15:00.000'

    UNION ALL SELECT '1900-01-01 18:30:00.000'

    UNION ALL SELECT '1900-01-01 18:45:00.000'

    UNION ALL SELECT '1900-01-01 19:00:00.000'

    UNION ALL SELECT '1900-01-01 19:15:00.000'

    UNION ALL SELECT '1900-01-01 19:30:00.000'

    UNION ALL SELECT '1900-01-01 19:45:00.000'

    UNION ALL SELECT '1900-01-01 20:00:00.000'

    UNION ALL SELECT '1900-01-01 20:15:00.000'

    UNION ALL SELECT '1900-01-01 20:30:00.000'

    UNION ALL SELECT '1900-01-01 20:45:00.000'

    UNION ALL SELECT '1900-01-01 21:00:00.000'

    UNION ALL SELECT '1900-01-01 21:15:00.000'

    UNION ALL SELECT '1900-01-01 21:30:00.000'

    UNION ALL SELECT '1900-01-01 21:45:00.000'

    UNION ALL SELECT '1900-01-01 22:00:00.000'

    UNION ALL SELECT '1900-01-01 22:15:00.000'

    UNION ALL SELECT '1900-01-01 22:30:00.000'

    UNION ALL SELECT '1900-01-01 22:45:00.000'

    UNION ALL SELECT '1900-01-01 23:00:00.000'

    ),

    sampleData (yourdatecol) AS (

    SELECT CAST(yourcharcol AS datetime)

    FROM sampleData1

    )

    SELECT *

    FROM sampleData

    See what I mean?

    -- Gianluca Sartori

  • Gianluca has a strong point - I recently ran a similar query (with days), and by continually running through and adding another day onto duplicate cases, I was filling in too many gaps and creating continuous ranges that could not possibly exist..

    Introducing a cut off point for adding another time point is essential when looking at this kind of query!

  • Gianluca Sartori (9/16/2011)


    ...

    Good catch, Gianluca !

    That is indeed a risk to assess, and that didn't even cross my mind. :crazy:

    As it appears, my test data hasn't been adequate enough to bring this caveat to the surface.

    It's a good thing I added

    TEST it, TEST IT

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 6 (of 6 total)

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