September 16, 2011 at 3:39 am
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
And what if the modified time (+1 min) is duplicate as well?
-- Gianluca Sartori
September 16, 2011 at 4:15 am
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
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
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
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
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