Check if date range is available?
-
Hi, I have a problem with generating script that will check during insert that my new date interval is available.
Here are the details:
I have a table with start and end date fields. When new record is inserted i need to check if it will not interfere with existing start/end date in the table. I can have up to 3 time slots for the same interval otherwise insert should fail.
Any instructions, scripts would be appreciated.
Thanks for you help.
-
SELECT startdt, enddt, jobnum
FROM dbo.jobschedule
startdt enddt jobnum
04/01/2004 04/02/2004 1
04/01/2004 04/02/2004 2
04/01/2004 04/02/2004 3
CREATE TRIGGER jobscheduletrigger
ON jobschedule
for insert
AS
declare @icount as integer
set @icount =
(select count (*)
from inserted i
inner join jobschedule j
on j.startdt = i.startdt
and j.enddt = i.enddt)
if @icount > 3
begin
raiserror 50001 'Exceeded maximum number of time slots for interval'
rollback transaction
return
end
insert into jobschedule
values
('4/1/2004', '4/2/2004', 4)
Server: Msg 50001, Level 16, State 1, Procedure jobscheduletrigger, Line 15
Exceeded maximum number of time slots for interval
SELECT startdt, enddt, jobnum
FROM dbo.jobschedule
startdt enddt jobnum
04/01/2004 04/02/2004 1
04/01/2004 04/02/2004 2
04/01/2004 04/02/2004 3
[font="Courier New"]ZenDada[/font]
-
Jules, thanks for the reply. Your solution will work only if the dates are the same.
If i try to run the following script,
insert into jobschedule values ('3/29/2004', '4/16/2004', 4)
error will not be raised even though the time slot between 4/1/2004 to 4/2/2004 is occupied 3 times.
Thanks for the try.
-
Let the point be that a trigger may be your answer. You can play with the logic yourself until it meets yours needs. Maybe like this?
SELECT startdt, enddt, jobnum
FROM dbo.jobschedule
startdt enddt jobnum
04/01/2004 04/02/2004 1
04/01/2004 04/02/2004 2
04/01/2004 04/02/2004 3
alter TRIGGER jobscheduletrigger
ON jobschedule
for insert
AS
declare @icount as integer
set @icount =
( select count (*)
from inserted i, jobschedule j
where (
j.startdt between i.startdt and i.enddt
and j.enddt between i.startdt and i.enddt
 
)
if @icount > 3
begin
raiserror 50001 'Exceeded maximum number of time slots for interval'
rollback transaction
return
end
insert into jobschedule
values
('3/29/2004', '4/16/2004', 4)
Server: Msg 50001, Level 16, State 1, Procedure jobscheduletrigger, Line 17
Exceeded maximum number of time slots for interval
SELECT startdt, enddt, jobnum
FROM dbo.jobschedule
startdt enddt jobnum
04/01/2004 04/02/2004 1
04/01/2004 04/02/2004 2
04/01/2004 04/02/2004 3
[font="Courier New"]ZenDada[/font]
-
Jules,
With a little "and to or" change, this trigger does the job.
Script below should cover possible situations to locate all reserved positions for specific date interval:
declare @start smalldatetime
declare @end smalldatetime
set @start = '4/7/2004'
set @end = '12/31/2004'
SELECT * FROM jobschedule
WHERE ((@start >= startdt AND @start <= enddt)
OR
(@end >= startdt AND @end <= enddt))
OR
((startdt >= @start AND startdt <= @end)
OR
(enddt >= @start AND enddt <= @end))
Thanks a lot for your help and guidance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply