將畸零數平均分攤到每一筆資料

2012-11-02

1167
0
T-SQL
2012-12-25

摘要:將畸零數平均分攤到每一筆資料

再進出貨的系統中, 常會遇到一個情形, 客戶折讓

可是往往折讓之後又要重新計算每一筆細項, 舉個例子

有一筆單據, 裡頭有三個品項, 金額各別是 400元, 400元, 500元

總金額應該是1300元, 客戶實際付費金額是 1000元所以折讓300元

客戶實際的金額是 307, 307, 384

問題是, 如過計算到整數位數, 會有 2塊錢的差額, 所以很多人就用各種方

法為了解決這個一兩塊錢的差額, 利用 SQL 2005的ROW_NUMBER函數

可以把這個差額平均分攤到各筆資料

--建立資料表 Grp, GrpDetail

Create Table Grp
(
grpid int primary key,
Amount int
)

Create Table GrpDetail
(
GrpId int,
ItemId int,
Amount int
)

Insert Into Grp(grpid, Amount)
Values(1, 1000), (2, 1500), (3, 2400)

Insert Into GrpDetail(GrpId, ItemId, Amount)
Values(1, 1, 400), (1, 2, 400), (1, 3, 500),
(2, 1, 600), (2, 2, 800), (2, 3, 500),
(3, 1, 1000), (3, 2, 1000)

;
With GrpTotal(grpId, Total)

-- 計算明細資料合計出來的各單據合計
as
(Select GrpId, SUM(Amount)
From GrpDetail
Group By GrpId
),
GTotal(GrpId, GrpAmount, Total, ItemId, Amount, ActAmount, rno)

--Join 計算出來的單據以及, 找出差額
as
(
Select g.grpid, g.Amount, gt.Total, gd.ItemId, gd.Amount,
ROUND(gd.Amount * g.Amount / gt.Total , 0, 1) as Act, -- 無條件捨入
ROW_NUMBER() Over (PARTITION By g.grpid Order By gd.ItemId)
From Grp g
Inner Join GrpTotal gt on g.grpid = gt.grpId
Inner Join GrpDetail gd on g.grpid = gd.GrpId
),
Diff(GrpId, GrpAmount, Diff)
as
(
Select GrpId, GrpAmount,GrpAmount - SUM(ActAmount) From GTotal
Group By GrpId, GrpAmount
)
Select *,
CASE WHEN rno <=Diff THEN ActAmount+1
ELSE ActAmount END as RealAmount
From GTotal gt
Left Join Diff d on gt.GrpId = d.GrpId

回首頁