Divided By The Biggest Gap

Problem posted by Jim Geissman

I have some data -- counts ID'd by location and grid East like this --
Loc East N
CA 100 3
CA 103 5
CA 109 2
CA 110 3


I'm interested in the total of N on either side of the largest gap in
Eastings.
In this case the largest gap is 6 (between 103 and 109), and the sum of
N for the 2 rows below the gap is 8, and for the 2 above the gap it's
5.


The problem is to locate the largest gap, and compute the sum of N for
the cases on either side. There are multiple locations, multiple
Eastings
per location, but only one largest gap. (If there are two largest
gaps, it
does't matter which one is used for the sums.)

The solution illustrates the power of OLAP functions:

CREATE TABLE a(Loc CHAR(2), East INT, N INT)
go
INSERT a VALUES('CA', 100, 3)
INSERT a VALUES('CA', 103, 5)
INSERT a VALUES('CA', 109, 2)
INSERT a VALUES('CA', 110, 3)
INSERT a VALUES('OR', 100, 3)
INSERT a VALUES('OR', 108, 5)
INSERT a VALUES('OR', 109, 2)
INSERT a VALUES('OR', 110, 3)
INSERT a VALUES('WA', 108, 5)
INSERT a VALUES('WA', 109, 2)
INSERT a VALUES('WA', 110, 3)
INSERT a VALUES('WA', 115, 3)


SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East > a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1


SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East > a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East > a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1


Loc East BeforeGap AfterGap GapSize rn
---- ----------- ----------- ----------- -----------
--------------------
CA 103 8 5 6 1
OR 100 3 10 8 1
WA 110 10 3 5 1


(3 row(s) affected)