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)
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.
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
