Post reply

Add one min when time same

  • September 16, 2011 at 3:39 am

    #257392

    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.

  • September 16, 2011 at 3:54 am

    #1382444

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

    -- Gianluca Sartori

  • September 16, 2011 at 4:15 am

    #1382449

    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

  • September 16, 2011 at 4:22 am

    #1382452

    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.. 😉

  • September 16, 2011 at 4:24 am

    #1382455

    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

  • September 16, 2011 at 4:35 am

    #1382461

    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!

  • September 16, 2011 at 6:48 am

    #1382511

    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 7 (of 7 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy