SQL Server validating overlapping dates, date ranges

by pencilvac 4/11/2012 5:55:00 AM

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.

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  July 2017  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign in