So it’s a long time since I wrote any SQL scripts, but I needed to be able to select a set of depth ranges not covered by a table. Lets say I have a table of rules:
Start | End | Rule
3 4 xxx
10 11 yyy
So what I want to see is the depths for which no rule is defined:
Start | End
0 3
4 10
11 null
So here it is:
DECLARE @MissingRanges TABLE (startDepth float, endDepth float);
-- Build a table of the missing data ranges that are between the defined rules
-- For each rule take the start depth of a rule as the end depth for a missing data range
INSERT INTO @MissingRanges(endDepth)
SELECT r1.StartDepth
FROM Rule r1;
-- Now we have the end depth for the missing ranges, find the defined rule with an end
-- depth immediately below that, or start from zero. Use a correlated subquery
UPDATE miss Set startDepth = COALESCE((Select Max(EndDepth)
FROM Rule r1
WHERE r1.EndDepth < miss.endDepth),0)
FROM @MissingRanges miss;
-- Add a last missing range below the deepest defined end depth
INSERT INTO @MissingRanges(startDepth)
SELECT COALESCE(MAX(r1.EndDepth), 0)
FROM Rule r1;
(
