Q&A
①引当可能数、引当待ち数、積送中数についての仕様はどのようなものでしょうか?
②セット品構成マスタと在庫マスタのサンプルデータから「最終的なイメージ」を作ることはできますか? 不可能な場合、「最終的なイメージ」を作成できるようなセット品構成マスタと在庫マスタのサンプルデータを提示できますか?
SQL Server上で拠点ごとの商品コード別在庫一覧表を作成したく、いい案が思いつかず皆様のお知恵をお貸しください。
最終的なイメージは以下の通りです
※商品コードで絞り込みを行います
例）品番「ABC-12345」で絞り込んだ場合
| 倉庫コード | 品番（親品番） | 在庫数 | 引当可能数 | 引当待ち数 | 積送中数 |
|---|---|---|---|---|---|
| 11 | ABC-12345 | 0 | 0 | 0 | 0 |
| 12 | ABC-12345 | 2 | 2 | 0 | 0 |
| 13 | ABC-12345 | 1 | 1 | 0 | 0 |
| 11 | DEF-67891 | 5 | 5 | 0 | 0 |
| 12 | DEF-67891 | 0 | 0 | 0 | 0 |
| 13 | DEF-67891 | 0 | 0 | 0 | 0 |
| 11 | XYZ-43210 | 0 | 0 | 0 | 0 |
| 12 | XYZ-43210 | 0 | 0 | 0 | 0 |
| 13 | XYZ-43210 | 3 | 0 | 0 | 0 |
| ・・・ |
例)在庫数、引当可能数の導き出し方
倉庫コード:11は構成に必要な[b-001][c-001]の在庫がないため、最小値[0]となり最終的な在庫数は[0]
倉庫コード:12は構成に必要な在庫が揃っておりそれぞれ、[a-001]が[8]、[b-002]が[5]、[c-001]が[10]で最小値は[5]
最小値[5]の[b-001]の構成数は[2] 割ると[2.5]
少数切り捨てのため最終的な在庫数は[2]
倉庫コード:13は構成に必要な在庫が揃っておりそれぞれ、[a-001]が[6]、[b-002]が[3]、[c-001]が[5]で最小値は[3]
最小値[3]の[b-001]の構成数は[2] 割ると[1.5]
少数切り捨てのため最終的な在庫数は[1]
上記結果を導くために以下のテーブルを使用します
※必要であると思われるカラムのみ表示してあります
1.セット品構成マスタ
| 親品番(SSOHCD) | 子品番(SSKHCD) | 構成数(SSINZU) |
|---|---|---|
| ABC-12345 | a-001 | 1 |
| ABC-12345 | b-001 | 2 |
| ABC-12345 | c-001 | 1 |
| DEF-67891 | d-001 | 2 |
| DEF-67891 | e-001 | 1 |
| DEF-67891 | f-001 | 2 |
| XYZ-43210 | x-001 | 2 |
| XYZ-43210 | y-001 | 1 |
| XYZ-43210 | z-001 | 1 |
| ・・・ |
2.在庫マスタ
| 倉庫コード(ZASKCD) | 品番(ZAHNCD) | 在庫数(ZAZKSU) | 引当可能数(ZAHHSU) | 引当待ち数(ZAHMSU) | 積送中数(ZASKSS) |
|---|---|---|---|---|---|
| 11 | a-001 | 10 | 10 | 0 | 0 |
| 12 | a-001 | 8 | 8 | 0 | 0 |
| 13 | a-001 | 6 | 6 | 0 | 0 |
| 12 | b-001 | 5 | 5 | 0 | 0 |
| 13 | b-001 | 3 | 3 | 0 | 0 |
| 12 | c-001 | 10 | 10 | 0 | 0 |
| 13 | c-001 | 5 | 5 | 0 | 0 |
| 11 | d-001 | 10 | 10 | 0 | 0 |
| 12 | d-001 | 8 | 8 | 0 | 0 |
| 13 | d-001 | 6 | 6 | 0 | 0 |
| 11 | e-001 | 10 | 10 | 0 | 0 |
| 12 | e-001 | 8 | 8 | 0 | 0 |
| 13 | e-001 | 6 | 6 | 0 | 0 |
| 11 | f-001 | 10 | 10 | 0 | 0 |
| 11 | x-001 | 10 | 10 | 0 | 0 |
| 12 | x-001 | 8 | 8 | 0 | 0 |
| 13 | x-001 | 6 | 6 | 0 | 0 |
| 12 | y-001 | 8 | 8 | 0 | 0 |
| 13 | y-001 | 6 | 6 | 0 | 0 |
| 11 | z-001 | 10 | 10 | 0 | 0 |
| 13 | z-001 | 6 | 6 | 0 | 0 |
| ・・・ |
[セット品構成マスタ]の[子品番(SSKHCD)]と[在庫マスタ]の[品番(ZAHNCD)]を紐づけます。
そして、[セット品構成マスタ]の[親品番(SSOHCD)]で絞り込みを行います。
・在庫マスタ内のデータはセット品構成が全て揃っていない場合があります。(拠点ごとにバラツキがある)
例:ABC-12345を構成するには[a-001][b-001][c-001]が必要
他の拠点ではセット品構成が揃っているにもかかわらず、拠点[11]では構成品の１レコードしか存在しない
この場合でもすべての構成があるように振舞い、最終的な拠点在庫を表示させる必要があります。
次の在庫数の式にも影響してきます。
・在庫数を表示させるロジック
拠点が所持している在庫数の最小値を取得しその値を構成数で割り、最終的な在庫数として算出いたします。
例:拠点[12]は構成の[a-001][b-001][c-001]を有しており在庫数の最小値は[1]
構成数を[1]で割る→1
※割った後の値が少数の場合は切り捨て
※引当可能数も同じように構成数で割ります
※書いていて気が付いたのですが、在庫数が同数で構成数が違う場合最終的にどのように算出されるのか。。。
SQLの仕様で抽出時の先頭レコードが採用される？(そうすると意図しない出力のされ方をされるのか？という疑問です)
この時、セット品構成が1つでも抜けていると構成の中に1つでも在庫が0のものがあっても
抜けがあることで在庫数があるように見えてしまう恐れがあるため、
構成品は全て揃っているうえで在庫数の算出を行う必要があります。
・引当可能数・・・在庫数に対して出荷可能な数値
・引当待ち数・・・受注後に在庫引当を待っている数値
・積送中数・・・出荷中の数値
以上になります。
これを1つのSQLで表現できるものなのでしょうか？
よろしくお願いいたします。
回答1件
0
ベストアンサー
もう少しシンプルに書けそうですが、たとえば以下のようなクエリはどうでしょうか? なお、引当待ち数と積送中数については算出方法が記載されていなかったので無視しています。
sql
1SELECT 2 T.倉庫コード, 3 T.親品番, 4 MIN(COALESCE(Z.在庫数, 0) / T.構成数) 構成数, 5 MIN(COALESCE(Z.引当可能数, 0) / T.構成数) 引当可能数 6FROM ( 7 SELECT DISTINCT Z.倉庫コード, S.親品番, S.子品番, S.構成数 8 FROM 在庫マスタ Z 9 CROSS JOIN セット品構成マスタ S 10) T 11LEFT OUTER JOIN 在庫マスタ Z ON T.倉庫コード = Z.倉庫コード AND T.子品番 = Z.品番 12GROUP BY T.倉庫コード, T.親品番 13ORDER BY T.親品番, T.倉庫コード
投稿2022/02/13 08:21
総合スコア2395
>>neko_the_shadow様
ご回答ありがとうございます。
少しだけ改良し、以下の3点を追記いたしました。
1.切り捨てのために４行目、５行目にFLOORを追加
2.８行目にFROM句の副問い合わせに親品番で絞り込むためにWHERE句を追加
3.在庫0のレコードは非表示にするために15行目にＷHERE句を追加
⇒ご提示いただいたクエリからイメージに近い値を出すことができましたが、このNo.3を追加した際に
実行後イメージと違うデータが抽出されました。
ちなみにFLOOR追加前は在庫のないデータにはメッセージ(後日確認します)が表示されており、
FLOOR追加後はメッセージ(後日確認します)が「0」に置き換わっておりました。
その後、15行目にＷHERE句を追加し実行すると在庫が「0」だったデータが
「1」と表示されたりと結果的に絞り込みが上手く行きません。
何か良い解決策はありますでしょうか。
SELECT
T.ZASKCD,
T.SSOHCD,
MIN(FLOOR(COALESCE(Z.ZAZKSU,0)/T.SSINZU)) AS ZAZKSU,
MIN(FLOOR(COALESCE(Z.ZAHHSU,0)/T.SSINZU)) AS ZAHHSU,
MIN(COALESCE(Z.ZAHMSU,0)) AS ZAHMSU,
MIN(COALESCE(Z.ZASKSS,0)) AS ZASKSS
FROM(
SELECT DISTINCT Z.ZASKCD,S.SSOHCD,S.SSKHCD,S.SSINZU
FROM MCMZAP Z
CROSS JOIN MCMSSP S
WHERE S.SSOHCD = '親品番をセット'
)T
LEFT OUTER JOIN MCMZAP Z ON T.ZASKCD = Z.ZASKCD AND T.SSKHCD = Z.ZAHNCD
WHERE ZAZKSU > 0
GROUP BY T.ZASKCD,T.SSOHCD
ORDER BY T.SSOHCD,T.ZASKCD
「在庫0のレコードは非表示にする」のであれば「WHERE ZAZKSU > 0」ではなく「HAVING MIN(COALESCE(Z.在庫数, 0) / T.構成数) > 0」にすればよいかと思います。
>>neko_the_shadow様
そうでした...GROUP BYを使っていましたね。もう一度初歩から勉強しなおします。
教えていただいた形で再編集するとイメージ通りの値が取得できました。
遅い時間にも関わらずご回答いただきありがとうございます。
無事このSQLで対応できそうです！
あなたの回答
tips
プレビュー
