SQL question

loyal ginger

I seem to be incapable of figuring out how to construct a query to achieve a simple thing. I was given a Microsoft Access table that looks like this: (This is just a simplified example)

RoadID Begin End

01005 0.0 2.3
01005 2.3 4.2
01005 4.2 10.7
01005 12.5 14.4
01005 14.4 16.9
01002 0.0 13.2
01002 13.2 16.2
01002 16.2 17.8
01002 17.8 21.2
02003 0.0 5.5
02003 7.2 10.2
02003 10.2 16.3
02003 16.3 27.8

Is it possible to make a query to find all the records that have a gap between it and the records before or after it (for each RoadID). The order of the original records should be sorted by RoadID and Begin. For this example, the output should be:

RoadID Begin End

01005 4.2 10.7
01005 12.5 14.4
02003 0.0 5.5
02003 7.2 10.2

In the above result, the first line has 10.7 as the End, and the second line has 12.5 as the Begin -- meaning that there is a gap, so both records should be shown. Same with the third and fourth lines. This is done for each RoadID, so for example, even though there is a gap between these two records (16.9 <> 0.0), they should not be shown in the result because their RoadID values are different (01005 and 01002):

01005 14.4 16.9
01002 0.0 13.2

I have tried to use some inner join tricks but could not get a thing. I could do this through a program, but I am curious to know if a single SQL query could achieve it. Any help on this is appreciated. Thanks!

Jorgen Andersson

How should the query react to overlaps?

List of common misconceptions

loyal ginger

Sorry I failed on making this point. The original question should be to find gaps and overlaps. Good point! Thanks!

Wendelius

You could try something like the following

SELECT a.*, b.*
FROM TableName a,
TableName b
WHERE a.roadid = b.roadid
AND b.start = (SELECT MIN(c.start)
FROM TableName c
WHERE c.roadid = a.roadid
AND c.Start > a.start)
AND a.End <> b.Start
ORDER BY ...

The need to optimize rises from a bad design.My articles[^]

Jorgen Andersson

Ok, this one works with your example: No it doesn't, it'll give you the combined stretches of road with gaps between them

WITH t1 AS (
SELECT r1.RoadID,r1.BEGIN
FROM Roads r1 Left outer join Roads r2
ON r1.RoadID = r2.RoadID
AND r1.BEGIN > r2.BEGIN
AND r1.BEGIN <= r2.END
GROUP BY r1.RoadID,r1.BEGIN
HAVING Count(r2.BEGIN) = 0
)
,t2 AS (
SELECT r3.RoadID,r3.END
FROM Roads r3 Left outer join Roads r4
ON r3.RoadID = r4.RoadID
AND r3.END >= r4.BEGIN
AND r3.END < r4.END
GROUP BY r3.RoadID,r3.END
HAVING Count(r4.BEGIN) = 0
)
,ContiguousStretch AS (
SELECT t1.RoadID,t1.BEGIN,Min(t2.END) AS END
FROM t1 join t2
ON t1.RoadID = t2.RoadID
AND t1.BEGIN <= t2.END
GROUP BY t1.RoadID,t1.BEGIN
)
,Gaps AS (
SELECT RoadID
FROM ContiguousStretch
GROUP BY RoadID
HAVING Count(RoadID) > 1
)
SELECT c.RoadID,BEGIN,END
FROM ContiguousStretch c join gaps g
ON c.RoadID = g.RoadID
ORDER BY c.RoadID,BEGIN

List of common misconceptions

modified on Thursday, March 31, 2011 3:45 PM

loyal ginger · modified on Thursday, March 31, 2011 3:45 PM

Thanks a lot!

loyal ginger

I appreciate your help. Thanks!

Jorgen Andersson

Take two, this one works with both overlaps and gaps and with no contiguous results:

WITH ordered AS(
SELECT RoadID,BEGIN,END,ROW_NUMBER() OVER(PARTITION BY RoadID ORDER BY BEGIN) as rn
FROM roads
)
SELECT o1.RoadID,o1.BEGIN,o1.END
FROM ordered o1,ordered o2
WHERE o1.RoadID = o2.RoadID
AND o1.rn = o2.rn -1
AND o1.END <> o2.BEGIN
UNION
SELECT o2.RoadID,o2.BEGIN,o2.END
FROM ordered o1,ordered o2
WHERE o1.RoadID = o2.RoadID
AND o1.rn = o2.rn -1
AND o1.END <> o2.BEGIN

If you want only the gaps or only the overlaps you vill have to change the conditions in o1.END <> o2.BEGIN

List of common misconceptions

modified on Thursday, March 31, 2011 4:49 PM

loyal ginger · modified on Thursday, March 31, 2011 4:49 PM

Thanks, Jörgen! BTW, I have been to Sverige (Göteborg, Stockholm, and Malmö) ten years ago. Beautiful places. I liked it there.