Sql-server – Check if contract conflict with another contract query

sql serverstored-procedurest-sql

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

enter image description here

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 =@old_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]
       ,[note]
       ,[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

Best Answer

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