我正在嘗試對同一個 select 陳述句中的另一個 sum 的值求和，然后我想檢查 case 陳述句中的 sum 值。當我這樣做時，它正在起作用，而只是獲得了個人價值。
我必須對 Billable_Trades 求和，然后如果 billable_trades 高于某些數字，我必須給出一些費率，我需要知道 billable_trade 的總數。
select t.Business_Unit_Description, -- case when Product_Type_Description = 'Fee Based' then 'Fee Based' else '' end as revenue_type,
billable_trades,
isnull(c.comm_adjustments, 0) as commission_adjustments,
rate,
billable_trades*rate as charges,
0.3 as commission_rate,
isnull(c.comm_adjustments, 0)*0.3 as credit,
(billable_trades*rate)- isnull(c.comm_adjustments, 0)*0.3 as total
from
(
select Business_Unit_Description,
sum(billable_trades) as billable_trades,
CASE WHEN SUM(billable_trades) > 0 and SUM(billable_trades) <= 150000 THEN 0.85667 ELSE 0.47104 END as rate
from cte_combined
group by Business_Unit_Description
) t
left outer join cte_comm_adj c on c.Business_Unit_Description = t.Business_Unit_Description
order by t.Business_Unit_Description
uj5u.com熱心網友回復：
查詢顯然比顯示的更多 - 因為您正在使用派生表來參考 CTE 以及外部連接到另一個 CTE。
我會將費率的計算移出派生表：
Select t.Business_Unit_Description -- case when Product_Type_Description = 'Fee Based' then 'Fee Based' else '' end as revenue_type,
, t.sum_billable_trades
, commission_adjustments = isnull(c.comm_adjustments, 0)
, r.rate
, charges = t.sum_billable_trades * r.rate
, commission_rate = 0.3
, credit = isnull(c.comm_adjustments, 0) * 0.3
, total = (t.sum_billable_trades * r.rate) - isnull(c.comm_adjustments, 0) * 0.3
From (Select Business_Unit_Description
, sum_billable_trades = sum(billable_trades)
From cte_combined
Group By Business_Unit_Description) t
Cross Apply (Values (iif(t.sum_billable_trades > 0 And t.sum_billable_trades <= 150000, 0.85667, 0.47104))) As r(rate)
Left Outer Join cte_comm_adj c On c.Business_Unit_Description = t.Business_Unit_Description
Order By t.Business_Unit_Description;
我也不會為總和使用相同的名稱，只是為了使其更清楚。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/397833.html
標籤：sql sql-server 查询语句
上一篇：未出現在輸出中的列名上的EXCEPT子查詢的內部聯接
下一篇：當資料庫兼容級別設定為SQLServer2008時，如何在SSISOLEDB源SQL命令中使用STRING_SPLIT？