-1

As the title says, I need to check if a start and end date period is within the range of another start and end date period. I don't know if this needs to be done in a trigger or a stored procedure.

For example, if the start and end date values of a row in table b are not between the start and end date values of a row in table a, then it should prevent it from insert.

The relevant tables may be described as follows:

--table a
a_code pk start_date end_date 

--table b 
b_code (pk) a_code (fk) start_date end_date

I saw this trigger example that I tried to apply to my situation,but it didn't work:

CREATE TRIGGER tI_Ranges ON 
Ranges INSTEAD OF INSERT AS 
    BEGIN IF (SELECT COUNT() FROM Ranges t 
                 CROSS APPLY inserted 
                       WHERE inserted.rangefrom 
                     BETWEEN t.rangefrom 
                         AND t.rangeto) = 0 
             AND (SELECT COUNT() FROM Ranges t 
                     CROSS APPLY inserted 
                        WHERE inserted.rangeto 
                      BETWEEN t.rangefrom AND t.rangeto) = 0 
             BEGIN INSERT INTO Ranges(rangefrom, rangeto) 
                   SELECT inserted.rangefrom, inserted.rangeto 
                     FROM inserted 
             END 
    ELSE BEGIN ROLLBACK TRAN END END
MDCCL
  • 8,520
  • 3
  • 30
  • 61
Yum
  • 1
  • 1
  • 1

1 Answers1

1

The simplest way to do this is to write a function that validates the dates are within the range and return a 0 or 1 based on the check. You then add a CHECK CONSTRAINT to your secondary table that validates that the function returns a 0. If not, then it prevents the insertion of data into the table.

Setup:

CREATE TABLE table_a (
    a_code INT IDENTITY PRIMARY KEY CLUSTERED,
    strt_date DATETIME,
    end_date DATETIME
);
GO

CREATE FUNCTION fnCheckDateRange (@id INT, @start_date DATETIME, @end_date DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @range_from DATETIME,
        @range_to DATETIME,
        @Return INT

    SELECT @range_from = strt_date, @range_to = end_date FROM table_a WHERE a_code = @id

    IF @start_date NOT BETWEEN @range_from AND @range_to AND @end_date NOT BETWEEN @range_from AND @range_to
    BEGIN
        SELECT @Return = 1
    END
    ELSE
    BEGIN
        SELECT @Return = 0
    END

    RETURN @Return
END
GO

CREATE TABLE table_b (
    b_code INT IDENTITY PRIMARY KEY CLUSTERED,
    a_code INT,
    strt_date DATETIME,
    end_date DATETIME,
    CONSTRAINT CHK_DateRange
    CHECK ( dbo.fnCheckDateRange(a_code, strt_date, end_date) = 0 ),
);

Test Data:

INSERT INTO table_a (strt_date, end_date)
VALUES ('2019-01-01', '2019-01-07'),
    ('2019-02-01', '2019-02-07'),
    ('2019-03-01', '2019-03-07')

Examples - Note, the third INSERT fails because the start and end dates are outside the range for the corresponding record in table_a:

INSERT INTO table_b (a_code, strt_date, end_date)
VALUES (1, '2019-01-01', '2019-01-07')

INSERT INTO table_b (a_code, strt_date, end_date)
VALUES (2, '2019-02-01', '2019-02-07')

INSERT INTO table_b (a_code, strt_date, end_date)
VALUES (3, '2019-02-01', '2019-04-07')
HandyD
  • 9,942
  • 1
  • 11
  • 27
  • The problem with this option is that the scalar function used will prevent any queries that touch the table from going parallel. A similar thing happens with computed columns. – Erik Darling May 23 '19 at 00:38
  • For reference: https://dba.stackexchange.com/questions/187342/is-there-a-way-to-prevent-scalar-udfs-in-computed-columns-from-inhibiting-parall – Erik Darling May 23 '19 at 00:40
  • A good point @ErikDarling - I suppose it is a question of functionality versus performance. A stored procedure for performing the insert could resolve both if the calling app can be altered to use an SP. – HandyD May 23 '19 at 00:42
  • Performance always seems to catch up with people :) – Erik Darling May 23 '19 at 00:50