Upland Consulting Group

When saving records to your database you may find that you cannot allow users to save multiple similar records with overlapping date ranges. The way I went about doing this was to check for the overlap in a SQL stored procedure in SQL server.

Out of all of these parameters If these parameters match a record that is already in the database, but without overlapping dates then we are good to save it. If however, the fields do match a record already in the database WITH overlapping dates, then we cannot save the record and we need to alert the user with an error.

To check for overlap I used an IF statement with a NOT EXISTS:

//Check if there is a similar record (matching fields) with overlapping dates.//

IF NOT EXISTS(select * from GroupFieldValues WHERE TargetEntityID = @TargetEntityID AND TargetPKID = @TargetPKID AND PKID = @PKID AND EntityID = @EntityID AND GroupFieldID = @GroupFieldID AND ( (@StartDate BETWEEN StartDate AND EndDate) OR (@EndDate BETWEEN StartDate AND EndDate) OR(@StartDate <= StartDate AND @EndDate >= EndDate) )

)

--Basically we have a similar record. If we do have a similar record lets see if their dates overlap. If the parameter startdate is between the records start and end then it must be overlapping. If the parameter endDate is between the records start and end then it has to be overlapping. Otherwise the only other situation I can think of is if the parameter startdate is before the records startdate and the parameter @EndDate is after the records EndDate which means overlap. If this does not exist then we can go ahead and save the record.

BEGINYOUR INSERT STATEMENT ENDIf it does exist ( an overlap) then we raise an error. ELSE BEGIN RAISERROR('Your new grouping was not saved because a similar group already exists within the same date range.',18,1) --Print'Date Confliction' END

ANOTHER TOPIC OF INTEREST: If you are doing an upsert stored procedure aka you are either inserting a new record or updating an already exisitng record with the same SPROC you will have to do a separate Date overlap test. Think of this situation: Your user goes into edit mode of a record in the front end of your application or website. The user changes the end date value to be November instead of October. When you check for overlap with the process above, the check will fail because there is a record with overlapping date ranges and your parameters (The record itself you are trying to update!). A way to get by this is performing separate checks based on whether you are trying to update or insert. Example:

IF @GroupFieldValueID = -1 --(our primary key) aka you are trying to insert a new record since its -1 (otherwise this will be the ID of the record you want to update) BEGIN --run our check for the new record IF NOT EXISTS(select * from GroupFieldValues WHERE TargetEntityID = @TargetEntityID AND TargetPKID = @TargetPKID --The company AND PKID = @PKID --specific Value of contract, Location, Pipeline, Pricing Area from Drop downs. AND EntityID = @EntityID --Entity were grouping on IE Pipeline or Contract AND GroupFieldID = @GroupFieldID AND ( (@StartDate BETWEEN StartDate AND EndDate) OR (@EndDate BETWEEN StartDate AND EndDate) OR(@StartDate <= StartDate AND @EndDate >= EndDate) )

) BEGIN --PRINT'Start if no date confliction'

YOUR INSERT STATEMENT

END

ELSE BEGIN RAISERROR('Your new grouping was not saved because a similar group already exists within the same date range.',18,1) --Print'Date Confliction' END

END ELSE--if @GroupFieldValueID != -1 meaning we are trying to update a record BEGIN --run a slightly different check where we specify that we are looking for an overlapping record that is NOT this record (AND GroupFieldValueID != @GroupFieldValueID) IF NOT EXISTS(select * from GroupFieldValues WHERE TargetEntityID = @TargetEntityID AND TargetPKID = @TargetPKID --The company AND PKID = @PKID --specific Value of contract, Location, Pipeline, Pricing Area from Drop downs. AND EntityID = @EntityID --Entity were grouping on IE Pipeline or Contract AND GroupFieldID = @GroupFieldID AND ( (@StartDate BETWEEN StartDate AND EndDate) OR (@EndDate BETWEEN StartDate AND EndDate) OR(@StartDate <= StartDate AND @EndDate >= EndDate) ) AND GroupFieldValueID != @GroupFieldValueID ) BEGINYOUR UPDATE STATEMENT END ELSE BEGIN RAISERROR('Your Group was not updated because there is another group with overlapping date ranges. ',18,1) END END END