SQL
案分
支払手数料は、支払いごとに一括で計上するのが普通だと思いますが、プロジェクトごとに分割することもあるようです。ただ、浮動小数点を扱う場合と違い、金額は整数なので、丸めによって誤差が生じます。その誤差をどう処理するか考えてみました。
３つのプロジェクトにかかわる支払いがあり、その支払手数料が525円の場合を例にあげます。
| プロジェクト |
支払額（税別） |
| A |
4,315円 |
| B |
1,777円 |
| C |
2,155円 |
| 合計 |
8,247円 |
| 支払手数料（税込） |
525円 |
→ |
支払手数料（税別） |
500円 |
| |
|
|
仮払消費税 |
25円 |
まず、支払手数料（税別）を支払額で重みづけして案分します。
| プロジェクト |
計算 |
支払手数料 |
| A |
500円×4,315円÷8,247円 |
261.6102円 |
| B |
500円×1,777円÷8,247円 |
107.7361円 |
| C |
500円×2,155円÷8,247円 |
130.6535円 |
案分した支払手数料の小数点以下を四捨五入します。
| プロジェクト |
支払手数料 |
四捨五入 |
| A |
261.6102円 |
262円 |
| B |
107.7361円 |
108円 |
| C |
130.6535円 |
131円 |
| 合計 |
|
501円 |
※例では、合計で１円の誤差が生じています。
ケース１ 誤差が０より大きい場合
支払手数料とその小数点以下を四捨五入したものとの差で大きいほうから並べ替えます。
| プロジェクト |
支払手数料 |
四捨五入 |
差 |
| A |
261.6102円 |
262円 |
0.3898円 |
| C |
130.6535円 |
131円 |
0.3465円 |
| B |
107.7361円 |
108円 |
0.2639円 |
| 合計 |
|
501円 |
|
上から誤差の値の行数だけ１を引いて補正します。
| プロジェクト |
支払手数料 |
四捨五入 |
補正後 |
| A |
261.6102円 |
262円 |
261円 |
← |
| C |
130.6535円 |
131円 |
131円 |
| B |
107.7361円 |
108円 |
108円 |
| 合計 |
|
501円 |
500円 |
※この場合、誤差は１なので、上から１行だけ１を引きます。例えば誤差が３であったなら（例の場合、四捨五入した値の合計が503円であったなら）、上から３行だけ四捨五入した値から１を引いて補正します。
ケース２ 誤差が０より小さい場合
支払手数料とそれを四捨五入したものとの差で小さいほうから並べ替えます。
上から誤差の値の行数だけ１を足して補正します。
ケース３ 誤差がない場合
ＯＫです。
SQLだとこんな感じです。
declare @data table (
idx int identity (0, 1),
project nvarchar(32),
payment money, -- 支払金額（税別）
charge1 money, -- 支払手数料（小数点以下あり）
charge2 money, -- 支払手数料（小数点以下なし）
error money) -- 小数点以下の誤差
declare @renum table (
idx int,
rowno int identity (0, 1))
declare @charge1 money -- 支払手数料
declare @charge2 money -- 合計 支払手数料
declare @amount money -- 合計 支払金額
declare @error money -- 支払手数料の誤差
set @charge1 = 500
insert into @data (project, payment) values (N'A', \4315)
insert into @data (project, payment) values (N'B', \1777)
insert into @data (project, payment) values (N'C', \2155)
--
-- 案分
--
select @amount = sum(payment) from @data
if @amount is not null and @amount != 0
begin
update @data set charge1 = (@charge1 * payment) / @amount -- 案分
update @data set charge2 = round(charge1, 0) -- 四捨五入
update @data set error = charge2 - charge1 -- 誤差
select @charge2 = sum(charge2) from @data -- 四捨五入したものを合計
set @error = @charge2 - @charge1 -- 全体の誤差
if @error > 0
begin
-- 誤差の大きい順に並べ替え
insert into @renum (idx) select idx from @data order by error desc
-- 上から誤差の値の行数だけ１を引く
update l set charge2 -= 1 from @data as l
join @renum as r on r.idx = l.idx where r.rowno < @error
end
else if @error < 0
begin
-- 誤差の小さい順に並べ替え
insert into @renum (idx) select idx from @data order by error
-- 上から誤差の値の行数だけ１を足す
update l set charge2 += 1 from @data as l
join @renum as r on r.idx = l.idx where r.rowno < -@error
end
end
--
-- 結果
--
select idx, project, payment, charge2 from @data
union all select max(idx) + 1, N'合計', sum(payment), sum(charge2)
from @data order by idx
| idx |
project |
payment |
charge2 |
| 0 |
A |
4315.00 |
261.00 |
| 1 |
B |
1777.00 |
108.00 |
| 2 |
C |
2155.00 |
131.00 |
| 3 |
合計 |
8247.00 |
500.00 |
支払いと支払手数料なので正の整数で考えていますが、その他の正負の整数を扱う場面でも応用できます。また、小数点以下の丸めは切り捨て（round(*,0,1)）でも同じ結果になると思います。