That will teach me to not give a real life example! :(

I see you rely on joining on ID+1, In the real world, the ID field is a uniqueidentfier so that is not going to work. Any ideas on how to adjust this?


>Hi Frank,
>
>

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

>
>MartinaJ
>
>>Hi,
>>
>>I need some help on how to calculate the freight charge on a package.
>>
>>I have a table like this :
>>
>>

ID, WeightLimit, FreightRate, FuelRate
>>1,     1,         12,          1.2
>>2,      50,        1,         0.1
>>3,     99999,    0.5,         0.05

>>
>>If I have a package weighing 2 kgs, then the total freight is calculated as:
>>
>>1st kg = 12
>>2nd kg = 1
>>Total = 13
>>
>>or if I had a package weighing 52 kgs:
>>1st kg = 12
>>next 49 kgs = 49 * 1 = 49
>>next 2kgs = 2 * 0.5 = 1
>>Total = 62
>>
>>Is there a way to achieve this in SQL?