不定期Access講座（運賃計算）_2

Accessで運賃計算をする講座（１）

不定期Access講座（運賃計算）_3

 

 

Access講座するって言いながら1週間が経ってしまった。

着手していた設計にイレギュラーがあってそちらの対応に追われていた。

 

設計が万全でも処理データが変化して来る事はあるし、

データが一定でも与件になかった設計漏れが有る事もあるし、

ほんと、データベースの設計って面倒くさい。

 

講座といっても１から１０まで網羅すると、このブログのペースなので1年はかかってしまうと思うので、ポイントだけ拾ってゆきたい。

 

実際は"まとめ送り状"や納期指定、発注〆時刻や固定運賃だったりといった諸条件があるけれど、今回はごく簡単な与件として下記を挙げる。

 

○データ与件

出荷日｜納品日｜送り状番号｜着地JISコード｜納品先名｜納品先住所｜個数｜重量｜下払業者コード

 

○設計与件

・収受料金は距離別重量単価とし、重量*単価で運賃を計算する。

・下払料金も距離別重量単価とし、重量*単価で運賃を計算する。

・距離程はJISコードから逆引きする（予めデータに距離程があれば必要ない）。

 

○着手

 

1-0テーブル設計

 

1-1距離程表

 

 

 

単純にJISコードから距離を逆引きする為のテーブル。収受運賃タリフの距離程とマッチングしなくても、ここでは無視。

 
ID着地JIS府県市区町村名距離着府県ｺｰﾄﾞJIS着府県名
1251 27102 大阪府大阪市都島区 17 27 大阪府
1252 27103 大阪府大阪市福島区 21 27 大阪府
1253 27104 大阪府大阪市此花区 28 27 大阪府
1254 27106 大阪府大阪市西区 21 27 大阪府
1255 27107 大阪府大阪市港区 21 27 大阪府
1256 27108 大阪府大阪市大正区 23 27 大阪府
1257 27109 大阪府大阪市天王寺区 23 27 大阪府

 

1-2収受運賃タリフ

 

元のタリフはEXCELで縦横列となっている。Vlookupで拾うのも手だけど、処理データを直接触るのは割と面倒臭いし、支払先や請求先が多岐に及ぶ程手間がかかってくる。またEXCELだと「送り先別1件集約」への対応で詰む。

f:id:sou:20140308000052p:plain

 

 

 

 

これをテーブルとして設計する場合、「10kmまでの運賃帯」を縦に並び替える。タリフがずっと10km刻みなら0km-10kmのレンジをわざわざ作る必要は無いと思う。今回はこの冗長性は使用しない。重量については、小数点第1位、第2位をする場合は、下限の重量に必要な小数点桁数を入力しておく。

 

 

 
ID1UnderDistanceUpperDistanceChargeUnderWeightUpperWeight
347 0 10 2.63 8000.01 9000
337 0 10 6.48 0 200
338 0 10 5.55 200.01 500
339 0 10 5.19 500.01 1000
340 0 10 3.72 1000.01 2000
341 0 10 3.66 2000.01 3000
342 0 10 3.51 3000.01 4000
343 0 10 3.07 4000.01 5000
344 0 10 2.86 5000.01 6000
346 0 10 2.68 7000.01 8000
348 0 10 2.25 9000.01 99999
345 0 10 2.81 6000.01 7000


 

1-3下払運賃タリフ 

 

元は収受運賃と同様、EXCELの縦横タリフ。

テーブルに直接作りこむと面倒くさいので、EXCELで業者別にシートで仕上げたものをインポートして１つのテーブルに集約している。 CODEは業者の固有コードとしている。

ID1UnderDistanceUpperDistanceChargeUnderWeightUpperWeightCODE備考
344 0 10 2.66 5001 6000 XEKNF06  
344 0 10 2.77 5001 6000 XEKNF02  
345 0 10 2.73 6001 7000 XEKNF02  
346 0 10 2.60 7001 8000 XEKNF02  
347 0 10 2.55 8001 9000 XEKNF02  
348 0 10 2.18 9001 99999 XEKNF02  
347 0 10 2.45 8001 9000 XEKNF06  
348 0 10 2.09 9001 99999 XEKNF03  
345 0 10 2.61 6001 7000 XEKNF06  
341 0 10 3.55 2000 3000 XEKNF02  
343 0 10 2.86 4001 5000 XEKNF06  
342 0 10 3.26 3000 4000 XEKNF06  
341 0 10 3.40 2000 3000 XEKNF06  
340 0 10 3.46 1000 2000 XEKNF06  
339 0 10 4.83 500 1000 XEKNF06  
338 0 10 5.16 200 500 XEKNF06  
337 0 10 6.03 0 200 XEKNF04  

 

とりあえずテーブルの準備が出来たところで眠くなった。

また次回。

 

 

 

以上でテーブルの構成は終了。