>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 #WL >>
ID, WeightLimit, FreightRate, FuelRate >>1, 1, 12, 1.2 >>2, 50, 1, 0.1 >>3, 99999, 0.5, 0.05>>