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 -- MathsHere 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.
精彩评论