问检查合同是否与另一个合同查询相冲突
EN

Database Administration用户

提问于 2021-04-17 16:52:42

回答 1查看 121关注 0票数 -1

如下图所示，我有一个带有这些表的数据库：

用户可以创建一个新的合同，每个合同可能有一个或多个商店和这些商店的状态变化取决于合同，一切都完美的工作到现在。我在续约的问题，我有一个功能，允许用户续签合同，在这种情况下，我得到所有的原始合同信息，并使新的合同从原始合同的结束日期+1天与相同的原始合同信息。

我在这里的问题是如何检查这个续约合同是否与同一客户和同一家商店的其他合同相冲突(客户可以有许多合同)。

例如：

我有这样的合同：

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

如果用户单击合同号1并尝试更新它，我想要一个查询来检查新合同开始日期是否与该用户和这些商店的其他合同冲突。

与我上面的数据一样，我希望阻止用户再次更新合同号1，因为在这段时间内已经有了续约的合同。

我创建了contracts_view，这个视图将发布的商店ID添加到合同中，因此它显示了合同的所有合同信息和相关的商店ID，视图如下：

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

这就是我试过的：

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

这是我续约的存储过程：

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

sql-server

t-sql

stored-procedures

回答 1

Database Administration用户

回答已采纳

发布于 2021-04-19 04:17:15

此查询将在contracts_view中搜索并检查其他任何活动合同之间的新合同开始日期：

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