T-SQL Update Query with DateTime Values

naisnet
naisnet used Ask the Experts™
on
I have a table:
pkey int, offset bit, startTime datetime, endTime datetime

I have to adjust the times by two minutes either way:
DATEADD(mi, 2, startTime), DATEADD(mi, -2, EndTime)

And mark the offset bit anywhere in the table that the adjusted times overlap with the times of any other record.  In the example below, the adjusted times in record 1000 overlap record 1002

1000 | 1 | 1/1/2001 12:00:00  | 1/1/2001 13:00:00
1001 | 0 | 1/1/2001 12:00:00  | 1/1/2001 15:00:00
1002 | 1 | 1/1/2001 12:00:00  | 1/1/2001 13:01:00

Just wondering what the best way to perform this query would be.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
Please try:
update TheTable
set startTime = DATEADD(mi, 2, startTime),
    endTime = DATEADD(mi, -2, endTime),
    offset = 1
from TheTable t1
where exists 
    (select 1 from TheTable t2 
      where t2.pkey <> t1.pkey
        and (   DATEADD(mi, 2, t1.startTime) between t2.startTime and t2.endTime
             or DATEADD(mi, -2, t1.endTime) between t2.startTime and t2.endTime))

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial