我需要根據重量和成本的計算創建一份報告。我做過的其他事情，但缺少的是如何計算成本，我不知道如何實作這一點。有人可以建議如何實作此輸出。
這是測驗資料。
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
預期產出
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
需要根據重量百分比計算 Linehaul、Fuel 和 Accessorial 成本。
例如： ID 1 的重量總和 = 11442 ID 2 的重量總和 = 15000
現在 5719/11442 = 50%
2727/11442 = 23%
3096/11442 = 27%
ID 1 的 Linehual 總成本 = 3099 ID 1
的燃料總成本 = 393.82 ID 1 的
輔助總成本 = 215
因此 Linehaul 成本將根據百分比計算在 3 個權重中分配
3099 * 50 % = 1548.96
3099 * 23 % = 711.51
3099 * 27 % = 838.53
對于燃料和附件成本以及不同的 ID，也將進行相同的計算。
uj5u.com熱心網友回復：
將視窗函式與sum()聚合一起使用。
按 ID 計算總重量
sum([Weight]) over (partition by ID)`
同樣，您可以使用CASE帶有視窗函式的運算式來查找 ID 類別的成本，例如 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 演示
uj5u.com熱心網友回復：
您可以使用視窗函式來計算每個 id 的總數。剩下的只是算術。如果我正確地遵循了您的邏輯：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/415066.html
標籤：