DECLARE @lfBASE INT SET @lfBASE=52 CREATE TABLE #WL (ID INT, WeightLimit INT, FreightRate FLOAT, FuelRate FLOAT) INSERT INTO #WL VALUES (1, 1, 12.0, 1.2), (2, 50, 1.0, 0.1), (3, 99999, 0.5, 0.05); WITH WL (ID, WeightLimit, FreightRate, FuelRate, DIFF, USED, FR_C) AS ( SELECt ID, WeightLimit, FreightRate, FuelRate, @lfBASE-(WeightLimit-0) AS DIFF, (WeightLimit-0) AS USED, FreightRate*(WeightLimit-0) AS FR_C FROM #WL WHERE ID=1 UNION ALL SELECT AA.ID, AA.WeightLimit, AA.FreightRate, AA.FuelRate, CASE WHEN WL.DIFF-(AA.WeightLimit-WL.WeightLimit)<0 THEN 0 ELSE WL.DIFF-(AA.WeightLimit-WL.WeightLimit) END AS DIFF, CASE WHEN WL.DIFF-(AA.WeightLimit-WL.WeightLimit)<0 THEN WL.DIFF ELSE AA.WeightLimit-WL.WeightLimit END AS USED, AA.FreightRate*CASE WHEN WL.DIFF-(AA.WeightLimit-WL.WeightLimit)<0 THEN WL.DIFF ELSE AA.WeightLimit-WL.WeightLimit END AS FR_C FROM #WL AA INNER JOIN WL ON AA.ID=WL.ID+1 AND WL.DIFF>0 ) SELECT ID, SUM(USED) AS USED, SUM(FR_C) AS FR_C FROM WL GROUP BY ROLLUP (ID) DROP TABLE #WLMartinaJ
ID, WeightLimit, FreightRate, FuelRate >1, 1, 12, 1.2 >2, 50, 1, 0.1 >3, 99999, 0.5, 0.05>