You can store pre-calculated gaps, and use constraints to make sure that your pre-calcualted data is always up-to-date:
Here is the table and the first interval
CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,
IntValue INT NOT NULL,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
PreviousFinishedAt DATETIME NULL,
CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),
CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),
CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt
FOREIGN KEY(SettingID, PreviousFinishedAt)
REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),
CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),
CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)
);
GO
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 1, '20070101', '20070103', NULL);
It has five constraints which work together to implement the business rule. Let me demonstrate how the more complex ones work. Of course, some constraints are simple and as such do not need any explanations.
**
- There can be only one first interval for a setting
**
The constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt ensures exactly that. The first interval does not have a previous one, which means that PreviousFinishedAt IS NULL. The UNIQUE constraint guarantees that there can be only one such row per setting. See for yourself:
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 1, '20070104', '20070105', NULL);
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.
The statement has been terminated.
*/
**
- Next window must begin after the end of the previous one.
**
The constraint CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt guarantees exactly that. See for yourself:
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 2, '20070104', '20070109', '20070105')
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.
The statement has been terminated.
*/
**
- Two different windows cannot refer to one and the same window as
their previous one.
**
Again, the same constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt guarantees precisely that, as demonstrated below:
INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 3, '20070104', '20070115', '20070103')
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.
The statement has been terminated.
This means that there can be no overlaps.
As you have seen, for every time window, there can be at most one preceding it, and at most one following it. The following interval cannot begin before the end of its previous one. Together these two statements mean that there can be no overlaps.
**
**
You can prohibit gaps altogether, just replace the following constraint:
CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),
With a stricter one, as follows:
CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),
But if you allow gaps, the query to retrieve them is very simple and performant, as follows:
SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd
FROM dbo.IntegerSettings
WHERE StartedAt > PreviousFinishedAt;
Best Answer
You can use
CROSS APPLY
to "unpivot" multiple columns. This query will give you a list of orders (unpivoted). I've added a third column, which is theLEAD
ing (i.e. next) OrderID, if there is any:This gives you:
Now, if we subtract the
OrderID
from the_nextOrderID
, we get the gap (how many orders are "missing"):.. which looks something like this:
So where _gap<=1, we don't want to return any rows. Where _gap=4, we want to insert 3 rows, from
OrderID+1
toOrderID+3
. There are a few ways to do this, but I'm going to stick withCROSS APPLY
here as well.I'm putting the query above in a subquery (called
sub
in my example), and for each row in that result, I'm going toCROSS APPLY
any dummy table. If you expect large gaps, you may want to create a separate table with a singleIDENTITY
column for this purpose, but I'm just going to use theOrders
table along with aROW_NUMBER()
:Finally, at the top, we add
n.rownum
tosub.OrderID
to get the missingOrderID
.Here's the final output: