I have a database with these tables as the bellow picture showed:
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)
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