Posted by Alex Kuznetsov on 10/09/06 16:46

Jim,

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)

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

• Next in forum: Re: How to connect to MSSQL server from HP-UX environment
• Prev in forum: Double summation
• Thread view: Re: Double summation

[Reply to this message]