Need to calculate cost based on Weight percentage-CodePudding

I needed to create a report based on the calculation of weight and cost. Other things I have done but how to calculate the cost is what is missing and I am not getting any idea how to achieve this. Can someone please suggest how to achieve this output.

Here is the test data.

Create table #temp
(
    ID int,
    StopNumber int,
    [Weight] int,
    Cost decimal (18,2),
    Category nvarchar(max)
)

Insert into #temp values (1,1,5719,3099,'Linehaul')
Insert into #temp values (1,2,2627,393.82,'Fuel')
Insert into #temp values (1,3,3096,215,'Accessorial')
Insert into #temp values (2,1,6000,4500,'Linehaul')
Insert into #temp values (2,2,5000,383.82,'Fuel')
Insert into #temp values (2,3,4000,315,'Accessorial')

select * from #temp

ID  StopNumber  Weight  Cost    Category 
1       1       5719    3099.00 Linehaul 
1       2       2627    393.82  Fuel 
1       3       3096    215.00  Accessorial 
2       1       6000    4500.00 Linehaul 
2       2       5000    383.82  Fuel 
2       3       4000    315.00  Accessorial

Expected output

ID  StopNumber  Weight  Cost    Category    LineHaul    Fuel    Accessorial  
1      1        5719    3099.00 Linehaul    1,548.96    196.84  107.46 
1      2        2627    393.82  Fuel        711.51      90.42   49.36 
1      3        3096    215.00  Accessorial 838.53      106.56  58.18 
2      1        6000    4500.00 Linehaul    1,800.00    153.53  126 
2      2        5000    383.82  Fuel        1,500.00    128     105 
2      3        4000    315.00  Accessorial 1,200.00    102.35  84

Need to calculate the Linehaul,Fuel and Accessorial cost based on weight percentage.

For eg : Sum of weight of ID 1 = 11442 Sum of weight of ID 2 = 15000

Now 5719/11442 = 50%
2727/11442 = 23%
3096/11442 = 27%

Linehual Total Cost for ID 1 = 3099
Fuel Total Cost for ID 1 = 393.82
Accessorial Total Cost for ID 1 = 215

So Linehaul cost will be distributed among 3 weight based on the percentage calculation

3099 * 50 % = 1548.96
3099 * 23 % = 711.51
3099 * 27 % = 838.53

Same calculation will be done for Fuel and Accessorial cost as well and for different ID.

CodePudding user response：

use window function with sum() aggregate.

For total weight by ID

sum([Weight]) over (partition by ID)`

Similarly you can use CASE expression with window function to find the cost for a category for a ID, example for Linehaul

sum(case when Category = 'Linehaul' then [Cost] end) over (partition by ID)

select  *,
        Linehaul    = [Weight] * 1.0 / sum([Weight]) over (partition by ID) 
                    * sum(case when Category = 'Linehaul' then [Cost] end) over (partition by ID),
        Fuel        = [Weight] * 1.0 / sum([Weight]) over (partition by ID) 
                    * sum(case when Category = 'Fuel' then [Cost] end) over (partition by ID),
        Accessorial = [Weight] * 1.0 / sum([Weight]) over (partition by ID) 
                    * sum(case when Category = 'Accessorial' then [Cost] end) over (partition by ID)
from    #temp t
order by ID, StopNumber

dbfiddle demo

CodePudding user response：

You can use window function to calculate the totals for each id. The rest is just arithmetic. If I'm following your logic correctly:

select t.*,
       (weight / id_linehaul) * (cost) as linehaul,
       (weight / id_fuel) * (cost) as fuel,
       (weight / id_accessorial) * (cost) as accessorial
from (select t.*,
             sum(weight) over (partition by id) as id_weight,
             sum(case when category = 'Linehaul' then cost end) over (partition by id) as id_linehaul,
             sum(case when category = 'Fuel' then cost end) over (partition by id) as id_fuel,
             sum(case when category = 'Accessorial' then cost end) over (partition by id) as id_accessorial
      from t
     ) t