SQLサーバーで抽出できますでしょうか？

Question

###前提・実現したいこと
以下のようなテーブルがあります。
|入出キー|品番|区分|単価１|数量１|単価２|数量２|入出額|差分
|:--|:--:|--:|
|1|Ｎ0001|1|141|320|142|220|76360|0
|2|Ｎ0001|2|NULL|40|NULL|NULL|5640|0
|3|Ｎ0001|1|138|500|NULL|NULL|69000|0
|4|Ｎ0001|2|NULL|100|NULL|NULL|14100|0
|5|Ｎ0001|2|NULL|200|NULL|NULL|28220|0
|6|Ｎ0001|2|NULL|50|NULL|NULL|7100|0
|7|Ｎ0001|2|NULL|650|NULL|NULL|90300|0

入出キー ：レコード毎にふられている数字
品番 ：品物に付けられている番号
区分 ：入荷・出荷の区分 １＝入荷、２＝出荷
単価１ ：入荷時の単価１
数量１（区分１の場合）：単価１の金額で入荷した数量
数量１（区分２の場合）：過去の入荷分（在庫）から出荷した数量
単価２ ：入荷時の単価２
数量２（区分１の場合）：単価２の金額で入荷した数量
数量２（区分２の場合）：過去の入荷分（在庫）から数量１とは違う単価で出荷した数量
入出額 ：入荷または出荷した金額
差分 ：（単価×数量）と入出額を比較した差分

※品番でGROUP BYしており、品番は全部で10000程あります。

###やりたいこと
品番ごとの各レコードの単価と数量をもとに額を出して、入出額と差分があるものを抽出したいです。

上記の表は、基本的に『（単価１×数量１）+（単価２×数量２）＝入出額』のような考え方になります。
ただ、単価に関しては、「先に入荷したものから出荷する」ので、出荷する数量によっては２重の単価になります。
以下に例を記載します。
●入出キー１
（141×320）+（142×220）＝76360
●入出キー２
（141×40）＝5640 ←単価は入出キー１の単価１で入荷した数量から40個
●入出キー３
（138×500）＝69000
●入出キー４
（141×100）＝14100 ←単価は入出キー１の単価１で入荷した数量から100個
●入出キー５
（141×180）+（142×20）＝28220 ←単価は入出キー１の単価１で入荷した数量から180個。単価１の数量を使い切ったので入出キー１の単価２で入荷した数量から20個
●入出キー６
（142×50）＝7100 ←単価は入出キー１の単価２で入荷した数量から50個
●入出キー７
（142×150）+（138×500）＝90300 ←単価は入出キー１の単価２で入荷した数量から150個。単価２の数量を使い切ったので、入出キー３の単価１で入荷した数量から500個

区分２（出荷）は、使用されている単価がレコードによって違う場合があるため、『（単価１×数量１）+（単価２×数量２）＝入出額』では単純に算出することが出来ません。

Excelにデータを吐き出して抽出しようとしたところ、上長よりＳＱＬで一覧にして欲しいとの指定があり、やり方が思いつきません。
いいやり方はありませんでしょうか？

上記の表は品番は１種類なのですが、実際のデータでは品番ごとにレコード数は１～５０レコードほどのデータになっており、レコード数は統一されていません。

###補足情報(言語/FW/ツール等のバージョンなど)
Microsoft SQL Server 2008 R2

Answer 1

SQLパズルのつもりで作成したら、「品番」の部分を失念していました。
時間切れなので、ヒントとして参考になればと回答しておきます。
ただこのSQLを上長に説明するのでしょうか…

手元の SQL Server 2016 で確認。2008では未確認です。

2017/06/27
品番について修正しました。

WITH WV AS(
  SELECT
    WT.[連番]
    ,WT.[入出キー]
    ,WT.[品番]
    ,WT.[区分]
    ,WT.[入出額]
    ,WT.[単価]
    ,WT.[数量]
    ,WT.[在庫数]
    ,WT.[入庫計]
    ,WT.[出庫計]
    ,LAG(WT.[入庫計] ,1 ,0) OVER(PARTITION BY WT.[品番] ORDER BY WT.[連番]) AS [L入庫計]
  FROM
    (
      SELECT
        MT.[連番]
        ,MT.[入出キー]
        ,MT.[品番]
        ,MT.[区分]
        ,MT.[単価]
        ,MT.[数量]
        ,MT.[入出額]
        ,SUM(MT.[数量]) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [在庫数]
        ,SUM(
          CASE
            WHEN MT.[数量] > 0 THEN MT.[数量]
            ELSE 0
          END
        ) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [入庫計]
        ,SUM(
          CASE
            WHEN MT.[数量] < 0 THEN - MT.[数量]
            ELSE 0
          END
        ) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [出庫計]
      FROM
        (
          SELECT
            ROW_NUMBER() OVER(ORDER BY ZT.[入出キー] ,T.[SEQ]) AS [連番]
            ,ZT.[入出キー]
            ,ZT.[品番]
            ,ZT.[区分]
            ,CASE
              WHEN [区分] = 1 THEN T.[数量]
              WHEN [区分] = 2 THEN - ZT.[数量1]
            END AS [数量]
            ,T.[単価]
            ,ZT.[入出額]
          FROM
            [在庫テーブル] ZT
            LEFT JOIN
              (
                SELECT
                  T1.[SEQ]
                  ,T1.[入出キー]
                  ,T1.[単価]
                  ,T2.[数量]
                FROM
                  (
                    SELECT
                      ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
                      ,[入出キー]
                      ,[単価]
                    FROM
                      [在庫テーブル] UNPIVOT([単価] FOR COLNAME1 IN([単価1] ,[単価2])) UP
                    WHERE
                      [区分] = 1
                  ) T1
                  INNER JOIN
                    (
                      SELECT
                        ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
                        ,[数量]
                      FROM
                        [在庫テーブル] UNPIVOT([数量] FOR COLNAME1 IN([数量1] ,[数量2])) UP
                      WHERE
                        [区分] = 1
                    ) T2
                  ON  T1.[SEQ] = T2.[SEQ]
              ) T
            ON  ZT.[入出キー] = T.[入出キー]
        ) MT
    ) WT
)
SELECT
  a.[連番]
  ,a.[入出キー]
  ,a.[品番]
  ,a.[区分]
  ,a.[入出額]
  ,a.[単価]
  ,a.[数量]
  ,a.[在庫数]
  ,a.[入庫計]
  ,a.[出庫計]
  ,(
    (
      SELECT
        SUM(b.[数量] * b.[単価])
      FROM
        WV b
      WHERE
        b.[連番] <= a.[連番]
      AND b.[数量] > 0
      AND b.[品番] = a.[品番]
    ) - CASE
      WHEN a.[出庫計] > 0 THEN ISNULL((
          SELECT
            SUM(b.[数量] * b.[単価])
          FROM
            WV b
          WHERE
            b.[連番] <= a.[連番]
          AND b.[数量] > 0
          AND b.[入庫計] <= a.[出庫計]
          AND b.[品番] = a.[品番]
        ) ,0) + ISNULL((
          SELECT
            (a.[出庫計] - b.[L入庫計]) * b.[単価]
          FROM
            WV b
          WHERE
            b.[連番] = (
              SELECT
                MIN(c.[連番])
              FROM
                WV c
              WHERE
                c.[連番] <= a.[連番]
              AND c.[入庫計] > a.[出庫計]
              AND c.[品番] = a.[品番]
            )
        ) ,0)
      ELSE 0
    END
  ) AS [金額]
FROM
  WV a
ORDER BY
  a.[品番]
  ,a.[連番]
;

Comments

[nua]

SQL考えてくださってありがとうございます。
作ってくださったものを参考にして書いてみます！
上長には、「作成するの結構大変です」と伝えていますので、成果物として一旦報告し、その上で指示を仰ぎたいと思います。
お時間頂きましたありがとうございました！

Answer 2

全体的になにをやりたいかわかりづらいですが
入荷金額に対する、原価計算をしたいならいまの管理方法では無理だと思います

まず品目マスターを用意し、仮想原価をいれておきます（仮に0円でもOK）
次に入荷したタイミングでマスタの原価を修正します。
初回発注については基本的に入荷金額で問題ないでしょう。
２回め以降で、マスタ原価と違う入荷単価の場合
(残在庫マスタ原価+入荷本数入荷単価)/在庫合計で新原価を決めて
マスタの原価を修正します。
出荷時にはマスタの原価を参照して、売上に原価を埋め込み、粗利計算します。

上記方式が常に正しい粗利を計算する方式ですが、実際に出入りが激しい場合
そこまで密に調整はできないと思います。
一般的には移動平均という方式で処理します。

また簡易的にやるために予定単価で全て計算し、適当なマイルストン
例えば、毎月月末や期末ごとに正規原価を振り直し、粗利差異を発生させるのが
会計上の処理になります。

Comments

[nua]

回答ありがとうございます。
質問の書き方が分かりづらくてすみませんでした。
テーブル的に原価計算をメインとした作りになっていないものでは、こういった抽出をするのには難しいんですね・・・

[yambejp]

現場として一つ一つの取引に対して粗利を計算したいのはわかります
それを実現するのが入ってきた金額をプールしていく方式か、移動平均です
ただ会計的にはそれはあまり意味がなく、入ってきた金額の合計金額と
出ていった金額の合計金額の差が利益になるので経理部門は
全体でしか勘定しません。

Answer 3

SQLServerも他のRDBと同様であるとして（SQLServer環境が無いので）、この仕様をSQLだけで実現するにはストアドプロシージャを使う方法が考えられますが、結構、難しいと思います。
先入れ先出のロジックをSQL文で書くのは結構苦労します。さらに入荷データが正規化されていない（単価２を別レコードにすると良い）のもSQL向きでは有りませんし。

Excelにデータを吐き出して抽出しようとしたところ、上長よりＳＱＬで一覧にして欲しいとの指定があり、やり方が思いつきません。

上長さんが、Excelでやろうとされている方に、SQLでと言われる意図は、「SQL＋Programの組み合わせ」かもしれませんのでご確認された方が良いと考えます。

Comments

[nua]

回答ありがとうございます。
ストアドはあまり得意ではないので、そんな私が書くのは厳しいですね・・・。
上長に確認したところ、他の検証抽出もＳＱＬで行なっているので、全項目を一覧にしたいそうで、excelでやるのは逆に工数が増えて手間になる・・・とのことでした。