-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
  • 1
    Could you post the schema and some mock-data? – Jonathan Fite May 22 at 19:30
  • I am usinf sql server – Yum May 22 at 19:32
  • ----table a---- a_code pk start_date end_date ----table b---- b_code (pk) a_code (fk) start_date end_date – Yum May 22 at 19:35
  • I saw this trigger example that i tried to apply to my situation. – Yum May 22 at 19:37
  • 1
    As for "then it should prevent it from insert", you want to prevent what from being inserted where? Please clarify. – MDCCL May 22 at 19:44
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')
  • 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 at 0:38
  • For reference: dba.stackexchange.com/questions/187342/… – Erik Darling May 23 at 0: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 at 0:42
  • Performance always seems to catch up with people :) – Erik Darling May 23 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.