Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Another sequencing algorithm

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 +
  
                  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

Print | posted on Tuesday, August 12, 2008 2:24 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET