Peter Larsson Blog

This problem originated here
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y
and I post the solution here for two reasons.

1) The forum above does not support code tags
2) The common interest is high enough

-- Prepare sample data
DECLARE @Sample TABLE
        ( 
            HoleID CHAR(8), 
            mFrom SMALLMONEY, 
            mTo SMALLMONEY, 
            Result SMALLMONEY, 
            PRIMARY KEY CLUSTERED 
            ( 
                HoleID, 
                mFrom 
            ), 
            Seq INT 
        )

INSERT  @Sample 
        ( 
            HoleID, 
            mFrom, 
            mTo, 
            Result 
        )
SELECT  'TWDD0004',   1   ,   2   ,  0.86 UNION ALL
SELECT  'TWDD0004',   3   ,   4   ,  8.93 UNION ALL
SELECT  'TWDD0004',   4   ,   5   ,  2.78 UNION ALL
SELECT  'TWDD0004',   8   ,   9   ,  1.21 UNION ALL
SELECT  'TWDD0004',  10   ,  11   ,  2.36 UNION ALL
SELECT  'TWDD0004',  11   ,  12   ,  0.86 UNION ALL
SELECT  'TWDD0004', 103   , 103.7 ,  0.5  UNION ALL
SELECT  'TWDD0004', 121.65, 122   ,  0.5  UNION ALL
SELECT  'TWDD0004', 130   , 131   ,  3.65 UNION ALL
SELECT  'TWDD0004', 131   , 131.5 ,  1    UNION ALL
SELECT  'TWDD0004', 132   , 133   ,  2.89 UNION ALL
SELECT  'TWDD0004', 133   , 134   ,  4.02 UNION ALL
SELECT  'TWDD0004', 134   , 135   ,  0.76 UNION ALL
SELECT  'TWDD0004', 138.3 , 139   ,  3.15 UNION ALL
SELECT  'TWDD0004', 139   , 140   ,  3.12 UNION ALL
SELECT  'TWDD0004', 140   , 141   ,  3.93 UNION ALL
SELECT  'TWDD0004', 141   , 142   ,  6.48 UNION ALL
SELECT  'TWDD0004', 142   , 143   ,  0.94 UNION ALL
SELECT  'TWDD0004', 155   , 156.15,  0.55 UNION ALL
SELECT  'TWDD0004', 164.9 , 165.75,  2.08 UNION ALL
SELECT  'TWDD0004', 166.9 , 167.9 ,  1.27 UNION ALL
SELECT  'TWDD0004', 167.9 , 169   ,  0.58 UNION ALL
SELECT  'TWDD0004', 170.9 , 171.25, 15    UNION ALL
SELECT  'TWDD0004', 185   , 186   ,  2.96 UNION ALL
SELECT  'TWDD0004', 186   , 187.3 ,  0.86 UNION ALL
SELECT  'TWDD0004', 187.3 , 188   ,  8.15 UNION ALL
SELECT  'TWDD0004', 188   , 188.45, 26.1  UNION ALL
SELECT  'TWDD0004', 188.45, 189   , 16.5  UNION ALL
SELECT  'TWDD0004', 189   , 189.9 ,  3.55 UNION ALL
SELECT  'TWDD0004', 189.9 , 190.2 ,  2.79 UNION ALL
SELECT  'TWDD0004', 190.2 , 191   ,  1.07 UNION ALL
SELECT  'TWDD0004', 191   , 191.85,  1.3  UNION ALL
SELECT  'TWDD0004', 191.85, 192.85,  2.22 UNION ALL
SELECT  'TWDD0004', 192.85, 193.3 ,  0.59 UNION ALL
SELECT  'TWDD0004', 193.3 , 194   ,  0.79 UNION ALL
SELECT  'TWDD0004', 194   , 194.85,  5.98 UNION ALL
SELECT  'TWDD0004', 194.85, 195.5 ,  0.82 UNION ALL
SELECT  'TWDD0004', 195.8 , 197   ,  7.03 UNION ALL
SELECT  'TWDD0004', 197   , 198   ,  3.84 UNION ALL
SELECT  'TWDD0004', 198   , 198.3 , 12.1  UNION ALL
SELECT  'TWDD0004', 198.3 , 199   ,  8.66 UNION ALL
SELECT  'TWDD0004', 199   , 200   ,  3.53 UNION ALL
SELECT  'TWDD0004', 200   , 201   ,  3.22 UNION ALL
SELECT  'TWDD0004', 201   , 202.1 ,  7.22 UNION ALL
SELECT  'TWDD0004', 202.1 , 202.55,  1.07

-- Initialize user supplied parameters
DECLARE  @WantedValue SMALLMONEY,
         @WasteValue SMALLMONEY

SELECT  @WantedValue = 4, 
        @WasteValue = 1

-- Prepare sequencing
DECLARE  @mTo SMALLMONEY, 
         @Seq INT,
         @HoleID CHAR(8)

-- Get initial values
SELECT TOP 1    @mTo = mTo, 
                @Seq = 0, 
                @HoleID = HoleID
FROM            @Sample
ORDER BY        HoleID, 
                mFrom

-- Update sequence
UPDATE  @Sample
SET     @Seq = Seq = CASE 
                        WHEN HoleID = @HoleID AND @mTo + @WasteValue >= mFrom THEN @Seq 
                        ELSE @Seq + 1 
                     END, 
        @HoleID = HoleID, 
        @mTo = mTo

-- Display the desired result
SELECT          HoleID, 
                MIN(mFrom) AS mFrom, 
                MAX(mTo) AS mTo, 
                AVG(Result) AS Result
FROM            @Sample
GROUP BY        HoleID, 
                Seq
HAVING          MAX(mTo) - MIN(mFrom) >= @WantedValue