Check for Date Overlap and Generate Error
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 tablePost 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);
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.
-- 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;
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?