请求开发含DATE OVERLAP校验的TestData表数据更新存储过程

创建一个存储过程，通过数据校验后更新[dbo].[TestData]表中的数据。该存储过程接收@UniqueID, @LocationID, @Start, @End作为输入参数。

注意：上述参数用于数据更新时，不得与表中其他行数据产生日期重叠（DATE OVERLAP）。

步骤概述 #

• 定位数据集（使用@LocationID、@Start、@End）
• 执行日期重叠检查：验证@Start和@End是否与数据表中其他行存在日期重叠
• 校验结果处理：
  • 若存在日期重叠（DATE OVERLAP = TRUE），抛出存储过程错误
  • 若不存在日期重叠（DATE OVERLAP = FALSE），关闭现有记录并创建新的单条记录

步骤1：定位数据集 #

检查是否存在符合指定日期范围（Start、End列）和LocationID的数据行，若存在则进入下一步。

示例筛选数据的输入参数及SQL语句：

@LocationID = 'A002';
@Start = '2023-03-01 14:30:00.000'
@End = '2023-03-01 16:32:00.000';

SELECT [UniqueID]
      ,[LocationID]
      ,[Start]
      ,[End]
      ,[IsCurrentRecord]
  FROM [dbo].[TestData]
  WHERE [LocationID] = 'A002'
  AND [Start] >= '2023-03-01 14:30:00.000'
  AND [End] <= '2023-03-01 16:30:00.000'

步骤2：日期重叠检查（待实现） #

执行日期重叠检查：将存储过程传入的@Start和@End日期值与同一LocationID下的其他记录进行对比。

场景一：抛出存储过程错误（存在日期重叠） #

输入参数：

@LocationID = A002
@Start = 01/03/2023 14:30
@End = 01/03/2023 16:31

若日期范围检查发现@Start和@End与数据表中其他行存在重叠（例如本案例中与UNIQUE_ID = 1005的记录重叠）：

01/03/2023 16:31 > 01/03/2023  16:30:03

此时需抛出存储过程错误。

场景二：关闭旧记录并生成新记录（无日期重叠） #

输入参数：

@LocationID = A002
@Start = 01/03/2023 14:30
@End = 01/03/2023 16:20

本案例中无日期重叠：

01/03/2023 16:25 < 01/03/2023  16:30:03

则执行以下操作关闭原始记录并创建新行：

关闭记录的更新脚本：

UPDATE [dbo].[TestData]
SET IsCurrentRecord = 0
WHERE LocationID = 'A002'
  AND [Start] >= '2023-03-01 14:30:00.000'
  AND [End] <= '2023-03-01 16:25:00.000'
GO

创建新行的插入语句：

INSERT INTO [dbo].[TestData]
([UniqueID]
    ,[LocationID]
    ,[start]
    ,[End]
    ,[IsCurrentRecord])
VALUES(
    1012 -- 取表中最大UniqueID加1生成
    ,'A002'
    ,'2023-03-01 14:30:00.000'
    ,'2023-03-01 16:25:00.000'
    , 1)

测试数据创建脚本 #

CREATE TABLE [dbo].[TestData](
    [UniqueID] [int] NULL,
    [LocationID] [varchar](50) NULL,
    [Start] [datetime] NULL,
    [End] [datetime] NULL,
    [IsCurrentRecord] [int] NULL
) ON [PRIMARY]
GO;

INSERT [dbo].[TestData] ([UniqueID], [LocationID], [Start], [End], [IsCurrentRecord])
VALUES
(1001, N'A001', CAST(N'2023-03-01T10:00:00.000' AS DateTime), CAST(N'2023-03-01T10:10:00.000' AS DateTime), 1),
(1002, N'A001', CAST(N'2023-03-01T10:00:00.000' AS DateTime), CAST(N'2023-03-01T10:10:00.000' AS DateTime), 1),
(1003, N'A002', CAST(N'2023-03-01T14:30:00.000' AS DateTime), CAST(N'2023-03-01T15:30:00.000' AS DateTime), 1),
(1004, N'A002', CAST(N'2023-03-01T15:30:00.000' AS DateTime), CAST(N'2023-03-01T16:20:00.000' AS DateTime), 1),
(1005, N'A002', CAST(N'2023-03-01T16:30:00.000' AS DateTime), CAST(N'2023-03-01T17:30:00.000' AS DateTime), 1),
(1006, N'A002', CAST(N'2023-03-01T17:30:00.000' AS DateTime), CAST(N'2023-03-01T18:30:00.000' AS DateTime), 1),
(1007, N'A002', CAST(N'2023-03-02T17:30:00.000' AS DateTime), CAST(N'2023-03-02T18:30:00.000' AS DateTime), 1),
(1008, N'A003', CAST(N'2023-03-01T20:30:00.000' AS DateTime), CAST(N'2023-03-01T20:35:00.000' AS DateTime), 1),
(1009, N'A003', CAST(N'2023-03-01T21:30:00.000' AS DateTime), CAST(N'2023-03-01T21:35:00.000' AS DateTime), 1),
(1010, N'A003', CAST(N'2023-03-01T22:30:00.000' AS DateTime), CAST(N'2023-03-01T23:35:00.000' AS DateTime), 1),
(1011, N'A001', CAST(N'2023-03-01T20:30:00.000' AS DateTime), CAST(N'2023-03-02T11:10:00.000' AS DateTime), 1),
(0, N'A002', CAST(N'2023-03-01T12:30:00.000' AS DateTime), CAST(N'2023-03-01T14:30:00.000' AS DateTime), 1);

内容的提问来源于stack exchange，提问作者AarionSQL