REQUIREMENT

Create a stored procedure to update data in a table [dbo].[TestData] post a successful data validation check.

The stored procedure will receive input parameters values for @UniqueID, @LocationID, @Start, @End .

The above parameters will be used to update the data and MUST not create a DATE OVERLAP with other rows of data in the table.

Brief outline of the steps:

Identify the dataset (using @LocationID, @Start, @End)
Perform a DATE OVERLAP check:
Check to see that the @Start or @End values are not present in any other rows of the data table
Post Validation Result

DATE OVERLAP = TRUE then generate error
DATE OVERLAP = FALSE then Close of existing records and create a new single record

STEP 1:Identify Dataset

Check if data rows exist for the date range (Columns: Start, End) and LocationID, if it exists then move on to next step

Eg, Input parameter values to select the data

@[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'

Step 2:Date Overlap Check - This is the part I need help on please

PERFORM A DATE OVERLAP CHECK: Compare the '@Start' and '@End' date value from passed as an input from the stored procedure to the other records for the same LocationID.

SCENARIO ONE: GENERATE stored procedure error (DATE OVERLAP = TRUE)

Input Parameters:

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

IF Date-Range-Check Column: @Start and @End is not present in any other rows in the data table

In this case there is an overlap with UNIQUE_ID = 1005.

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

Generate stored procedure error.

SCENARIO TWO: CLOSE OLD RECORD. GENERATE NEW RECORD (DATE OVERLAP = FALSE)

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

In this case there is no overlap in the date

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

Then Close original records and create a new row as shown below:

Below is a script to update (Close records):

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

Below is an INSERT statement to create the New row:

INSERT INTO [dbo].[TestData]
  ([UniqueID]
      ,[LocationID]
      ,[start]
      ,[End]
      ,[IsCurrentRecord])
  VALUES(
      1012 --MaxID + 1  generated
      ,'A002'
      ,'2023-03-01 14:30:00.000'
      ,'2023-03-01 16:25:00.000'
      , 1)

Script to create test data:

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

-- SETUP THE DATA CREATE TABLE [TestData]( [UniqueID] [int] not NULL identity(1,1), [LocationID] [varchar](50) NULL, [Start] [datetime] NULL, [End] [datetime] NULL, [IsCurrentRecord] [int] NULL ); SET IDENTITY_INSERT [TestData] ON; INSERT [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), -- Whats with the following row??? (0, N'A002', CAST(N'2023-03-01T12:30:00.000' AS DateTime), CAST(N'2023-03-01T14:30:00.000' AS DateTime), 1); SET IDENTITY_INSERT [TestData] OFF; -- PROCEDURE LOGIC -- Failure case declare @LocationID varchar(50) = 'A002', @StartDateRange datetime = '2023-03-01 14:30:00.000', @EndDateRange datetime = '2023-03-01 16:31:00.000'; -- Success case --declare @LocationID varchar(50) = 'A002', @StartDateRange datetime = '2023-03-01 14:30:00.000', @EndDateRange datetime = '2023-03-01 16:25:00.000'; with cte as ( -- Don't filter by end date here because we need it for the lead window function -- Instead filter from the results of the CTE select * , lead([Start]) over (partition by LocationId order by [Start] asc) NextStartDate from TestData where LocationId = @LocationId and [Start] >= @StartDateRange ) select UniqueId, LocationId , min([Start]) over () MinStartDate , @EndDateRange EndDateRange , max(NextStartDate) over () NextStartDate into #temp from cte -- You normally make the end date non-inclusive where [End] <= @EndDateRange; -- I suspect you want to add another check here for if only a single row was selected, because if only a single row was selected there would be no need to update it? if not exists (select 1 from #temp) begin throw 51000, 'No Rows Selected', 1; end; else if exists (select 1 from #temp where coalesce(NextStartDate, EndDateRange) < EndDateRange) begin throw 51000, 'Date Overlap Error', 1; end; else begin -- Update existing rows update t set IsCurrentRecord = 0 from TestData t join #temp tm on tm.UniqueID = t.UniqueID; -- Insert new row -- Assuming UniqueID is an identity -- We can use top 1 because the values we need are identical in all records insert into TestData (LocationID, [Start], [End], [IsCurrentRecord]) select top 1 LocationID, MinStartDate, EndDateRange, 1 from #temp; end; select * from TestData;

1条答案

按热度按时间

x9ybnkn61#

I've answered this because I invested a lot of time on understanding your requirements and didn't want that time to go to waste.

But I seriously recommend learning how to write a good, clear question, by looking at other well received questions.

Also please do not delete a question and repeat it, rather improve your existing question and then if clear it will get reopened.

The steps of logic are:

Identify the rows that meet your criteria AND check the row following those rows using LEAD .
Store the results in a temp table since you need to perform 2 actions if the validation passes.
Validate the results and throw errors as required.
If validation passes, update the rows selected in step 1 and insert the new row as specified.

db<>fiddle

赞(0）回复(0）举报 2023-06-04

SQL Server Check for Date Overlap and Generate Error

1条答案

相关问题

热门标签

最新问答