Check if contract conflict with another contract query

All we need is an easy explanation of the problem, so here it is.

I have a database with these tables as the bellow picture showed:

Check if contract conflict with another contract query

The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts, everything works perfectly until now.
my problem in renewing the contract, I have a feature that allows the user to renew a contract, in this case, I get all the original contract info and make the new contract start from the end date of the original contract + 1 day with the same original contract info.

my question here is how to check if this renewal contract does not conflict with other contracts for the same customer and the same shops (customer can have many contracts)

E.G.:

I have contracts like this:

con_id    shop_id   start_date    end_date
--------------------------------------------
  1         1       14-04-2021    14-04-2022
  2         1       15-04-2022    15-04-2023

If the user clicked on contract number 1 and tried to renew it, I want a query to check if the new contract start date does not conflict with another contract for this user and these shops.

Like in my above data I want to prevent the user from renewing contract number 1 again because there is already a contract renewed before in that period.

I created contracts_view, this view add the releted shops ID to the contracts so it shows all contract information and the related shops ID for the contract, and the view as this:

SELECT CN.ID, CN.cust_id, C.name, CSH.shop_id, CN.duration, CN.price, CN.tax, 
CN.usage, CN.rent_type, CN.price2, CN.note2, CN.date_start, CN.date_end, 
CN.note, CN.app_user
FROM contracts CN CROSS apply
(SELECT STRING_AGG(CSH.shop_id, '-') AS shop_id FROM contracts_shops CSH 
 WHERE CSH.contract_id = CN.ID) CSH CROSS apply
(SELECT C.name AS name FROM customers C WHERE C.cust_id = CN.cust_id) C

This is what I tried:

--IF EXISTS (SELECT * FROM contracts_view where cust_id=123456789 and @date_start >= date_start and @date_start <= date_end and shop_id in (select shop_id from contracts_shops where contract_id [email protected]_contract_id)) 
--BEGIN
--    SELECT @ErrorMessage = ERROR_MESSAGE()
--  RAISERROR ('asdasd', 10, 1)
--  ROLLBACK TRAN
--   return
--END

and here is my stored procedure for renewing a contract:

ALTER PROCEDURE [dbo].[contract_renew]
-- Add the parameters for the stored procedure here
@cust_id int,
@duration int,
@price decimal(10,2),
@tax decimal(10,2),
@usage nvarchar(20),
@rent_type nvarchar(10),
@price2 decimal(10,2),
@note2 nvarchar(max),
@date_start date,
@date_end date,
@note nvarchar(max),
@app_user nvarchar(20),
@old_contract_id int
AS
BEGIN

DECLARE @ErrorMessage NVARCHAR(MAX)
DECLARE @ID int

BEGIN TRAN
BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--insert data
INSERT INTO [dbo].[contracts]
       ([cust_id]
       ,[duration]
       ,[price]
       ,[tax]
       ,[usage]
       ,[rent_type]
       ,[price2]
       ,[note2]
       ,[date_start]
       ,[date_end]
       ,
,[app_user]) VALUES (@cust_id, @duration, @price, @tax, @usage, @rent_type, @price2, @note2, @date_start, @date_end, @note, @app_user) SELECT SCOPE_IDENTITY(); SET @ID = SCOPE_IDENTITY(); insert into contracts_shops (contract_id, shop_id) select @ID, shop_id FROM contracts_shops WHERE contract_id = @old_contract_id; COMMIT END TRY BEGIN CATCH SELECT @ErrorMessage = ERROR_MESSAGE() RAISERROR (@ErrorMessage, 10, 1) ROLLBACK TRAN END CATCH END

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

this query will search in contracts_view and check if the new contract start date between any other active contracts:

declare @d as date = '2025-04-22'

IF EXISTS (SELECT * FROM contracts_view where contracts_view.cust_id=123456789 and @d >= contracts_view.date_start and @d  <= contracts_view.date_end 
and contracts_view.shop_id = (SELECT STRING_AGG(shop_id,'-') shop FROM contracts_shops where contracts_shops.contract_id= 1023 GROUP BY contract_id)) 
BEGIN
    print 'Cannot add'
END
else
BEGIN
    print 'added'
END

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply