开发者

SQL Time Overlap/Conflict Query

开发者 https://www.devze.com 2023-02-14 09:31 出处:网络
I have a requirement where I need to assign some resource for some tine frame .For example Existing in Database : John Smith -- 3/1/2011 -- 6:00 AM To 7:00 AM -- Economics

I have a requirement where I need to assign some resource for some tine frame .For example

Existing in Database : John Smith -- 3/1/2011 -- 6:00 AM To 7:00 AM -- Economics

To Be Inserted: John Smith -- 3/1/2011 -- 6:30 AM to 7:00 AM -- Maths

Here while inserting second row I want to check this time overlap if its overlapping I want to skip insert.How can I achieve this ?I hav开发者_开发技巧e Date , FromTime and ToTime in seperate columns.


use computed dates using a conversion:

cast(cast(mydatecolumn as varchar(20)) + ' ' + 
 cast(mytimecolumn as varchar(20)) as datetime)

yields e.g. start_datetime and end_datetime

assume variables: @inserted_start_datetime, @inserted_end_datetime, @name

Insert Into MyTable(<values>)
Where not exists ( 
 select * from table t2 
 where name = @name 
 and (@inserted_start_datetime between start_datetime and end_datetime 
  or @inserted_end_datetime between start_datetime and end_datetime 
  or start_datetime between @inserted_start_datetime and @inserted_end_datetime 
  or end_datetime between @inserted_start_datetime and @inserted_end_datetime ))

EDITED


if your from to dates are timedates in the database you could try something like this

if not exists ( select * from table where name = @name and @startTime between startTime and endTime or @endTime between startTime and endTime ) begin

do insert.

where @name = John Smith @startTime = 3/1/2011 6:30 @endTime = 3/1/2011 7:00

end


Basically like this:

INSERT INTO atable (Name, Date, FromTime, ToTime, Subject)
SELECT
  @Name,
  @Date,
  @FromTime,
  @ToTime,
  @Subject
WHERE NOT EXISTS (
  SELECT * FROM atable
  WHERE Name = @Name AND Date = @Date
    AND (FromTime >= @FromTime AND FromTime < @ToTime  OR
         @FromTime >= FromTime AND @FromTime < ToTime)
)

You might want to add some data conversion if and where needed.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号