DECLARE @lfBASE INT SET @lfBASE=52 declare @olrpk uniqueidentifier set @olrpk = '4C2F4DFD-E052-4362-8E5F-00E2F6BA0CBE' ;WITH WL (olb_pk, olb_WeightLimit, olb_commrate, DIFF, USED, Freight) AS ( SELECt olb_pk, olb_WeightLimit, olb_commrate, @lfBASE-(olb_WeightLimit-0) AS DIFF, (olb_WeightLimit-0) AS USED, olb_commrate*(olb_WeightLimit-0) AS Freight FROM OutboundLIATBands inner join (select MIN(olb_WeightLimit) as minweight from OutboundLIATBands a where a.olb_olrfk = @olrpk) b on b.minweight = OutboundLIATBands.olb_weightlimit and olb_olrfk = @olrpk UNION ALL SELECT AA.olb_pk, AA.olb_WeightLimit, AA.olb_commrate, CASE WHEN WL.DIFF-(AA.olb_WeightLimit-WL.olb_WeightLimit)<0 THEN 0 ELSE WL.DIFF-(AA.olb_WeightLimit-WL.olb_WeightLimit) END AS DIFF, CASE WHEN WL.DIFF-(AA.olb_WeightLimit-WL.olb_WeightLimit)<0 THEN WL.DIFF ELSE AA.olb_WeightLimit-WL.olb_WeightLimit END AS USED, AA.olb_commrate*CASE WHEN WL.DIFF-(AA.olb_WeightLimit-WL.olb_WeightLimit)<0 THEN WL.DIFF ELSE AA.olb_WeightLimit-WL.olb_WeightLimit END AS Freight FROM OutboundLIATBands AA INNER JOIN WL ON AA.olb_olrfk = WL.olb_pk AND AA.olb_weightlimit > WL.USED AND WL.DIFF>0 ) SELECT olb_pk, SUM(USED) AS USED, SUM(Freight) AS Freight FROM WL group by rollup (olb_pk)The problem I am having is second SELECT: I do not know what I should use here to get the next limit/band to join properly.
>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>>