Check SQL Server Date / Time Overlap for Event Planning

Question

I have an event table that has the following columns:

StartDate datetime EndDate datetime StartTime nvarchar(10) EndTime nvarchar(10) 

Let's say the entry has the following values ​​in the table:

 StartDate: 2011-12-22 00:00:00.000 EndDate: 2011-12-22 00:00:00.000 StartTime: 4:00 PM EndTime: 5:00 PM 

Now, if someone comes to schedule an event, I need to make sure that he cannot schedule it at the same time or between the start and end times of this existing event.

So, he can plan from 17:00 to 18:00 or from 11:00 to 16:00, but not from 11:00 to 17:00 or from 16:30 to 17:00.

How can I check this? I know that this is not the best design for this table, but I must adhere to this design, otherwise we will have to change a lot of middle and client code.

Answer 1

try it

 declare @apptDate DATETIME SET @apptDate = '12/22/2011 4:30PM' select COUNT(*) from t1 where @apptDate Between CAST( StartDate+StartTime as DateTime) and CAST( EndDate+EndTime as DateTime) SET @apptDate = '12/22/2011 6:30PM' select COUNT(*) from t1 where @apptDate Between CAST( StartDate+StartTime as DateTime) and CAST( EndDate+EndTime as DateTime) 

If count (*)> 0, there is a meeting. If not, the stain is free

Answer 2

When you add a new record or change an existing one, you can check that your start or end timestamps do not fall into existing time ranges, and the new range does not cover existing ones.

Assuming this date also matters not only in time, but also in the best way to store start and end dates and times as DATETIME columns, for example StartDT and EndDT - in this case you need to check non-overlapping other rows with this operator

 Where not exists( select * from yourtable where (@newstart >= StartDT AND @newstart < EndDT) OR (@newend > StartDT AND @newend <= EndDT) OR (@newstart <= StartDT AND @newend >= EndDT) ) 

Of course you can rewrite it as an EXISTS clause instead of NON EXISTS

So, if you cannot change the schema, you should simply change StartDT and EndDT to expressions that make up the corresponding values ​​from the base columns of the row, for example

  StartDT = SUBSTRING(StartDate, 1, 10) + ' '+ StartTime 

Answer 3

I just want to send my request here - maybe someone can help. It works like a charm. Thanks for the help!

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PS_Event_Insert] ( @UserID int, @AmenityID int, @DateFrom datetime, @DateTo datetime, @TimeFrom nvarchar(50), @TimeTo nvarchar(50), @Description nvarchar(max), @IsPrivate bit, @NumberOfPeople nvarchar(100), @Food bit, @StatusID int, @Notes nvarchar(500) = NULL, @EventID int OUTPUT ) AS declare @newdtfrom datetime = cast(@DateFrom + ' ' + @TimeFrom as datetime) declare @newdtto datetime = cast(@DateTo + ' ' + @TimeTo as datetime) DECLARE @RecCount int BEGIN SET @RecCount = ( select count(*) from [events] where (@newdtfrom >= cast([datefrom] + ' ' + [timefrom] as datetime) and @newdtfrom <= cast([dateto] + ' ' + [timeto] as datetime)) or (@newdtto > cast([datefrom] + ' ' + [timefrom] as datetime) and @newdtto <= cast([dateto] + ' ' + [timeto] as datetime)) or (@newdtfrom <= cast([datefrom] + ' ' + [timefrom] as datetime) and @newdtto >= cast([dateto] + ' ' + [timeto] as datetime)) AND [AmenityID] = @AmenityID AND [StatusID] = 5 ) IF (@RecCount = 0) BEGIN INSERT INTO [Events] ( [UserID], [AmenityID], [DateFrom], [DateTo], [TimeFrom], [TimeTo], [Description], [IsPrivate], [NumberOfPeople], [Food], [StatusID], [Notes] ) VALUES ( @UserID, @AmenityID, @DateFrom, @DateTo, @TimeFrom, @TimeTo, @Description, @IsPrivate, @NumberOfPeople, @Food, @StatusID, @Notes ) SET @EventID = SCOPE_IDENTITY() END ELSE BEGIN SET @EventID = -999 END END