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.
ALTER PROCEDURE [dbo].[UpsertGroupFieldValues]
@GroupFieldValueID int = -1,
@TargetEntityID int,
@TargetPKID int,
@PKID int,
@EntityID int,
@GroupFieldID int,
@StartDate DateTime,
@EndDate DateTime,
@UserID int,
@GroupValueIDReturned int OUTPUT
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.
BEGIN
YOUR INSERT STATEMENT
END
If 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
)
BEGIN
YOUR 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
BOOOM no more conflicting date ranges.
Subscribe