SQL 振り分け方法について

Question

前提・実現したいこと

下記の図のような結果をSQLだけで導き出したいのですが
お力をお借りさせていただきたいです。
使用しているのはSQL SERBERです。

<現在のDB>
【テーブル名：入荷予定】
OrderNo | RowNo | ItemID | YoteiCnt | TorikomiFile
1000001 | 1 | 1 | 5 | A
1000001 | 2 | 2 | 5 | A
1000001 | 3 | 3 | 5 | A
1000001 | 4 | 1 | 5 | B

【テーブル名：入荷状況】
OrderNo | ItemID | NyukaCnt |
1000001 | 1 | 7 |
1000001 | 2 | 5 |
1000001 | 3 | 5 |

<実現したい結果>
OrderNo 1000001で入荷したItemID 1 のNyukaCnt 7 を
OrderNo 1000001 内にあるItemID 1 の2行に対して振り分けを行い、
1行目は5、4行目は2という結果を導く。
※引当順はRowNoの昇順
結合条件：
入荷予定.OrderNo = 入荷状況.OrderNo
入荷予定.ItemID = 入荷状況.ItemID

【算出結果】
OrderNo | RowNo | ItemID | YoteiCnt | TorikomiFile | NyukaCnt | Kabusoku
1000001 | 1 | 1 | 5 | A | 5 | 0
1000001 | 2 | 2 | 5 | A | 5 | 0
1000001 | 3 | 3 | 5 | A | 5 | 0
1000001 | 4 | 1 | 5 | B | 2 | -3

該当のソースコード

/*--２つのテーブルの結合までは導き出せるのですが
振り分けるとなるとどのようにクエリを書けばいいのかわからず、
アドバイスをいただけますと幸いです。--*/ 

SELECT
        TNY.OrderNo
        , TNY.ItemID
        , TNY.YoteiCnt
        , TNY.TorikomiFile
        , TNJ.NyukaCnt
        , (TNJ.NyukaCnt - TNY.YoteiCnt) AS Kabusoku 
    FROM
        入荷予定 TNY 
        LEFT JOIN 入荷状況 TNJ 
            ON TNY.OrderNo = TNJ.OrderNo 
            AND TNY.ItemID = TNJ.ItemID;

Answer 1

先ずは、YoteiCntの累計を求めれば良さそうです。
累計はWindow関数を用いるとコストが少なくて済みます。
SELECT - OVER 句 (Transact-SQL)
その累計とNyukaCntの合計を比較、それぞれで演算します。

SELECT  TNY.OrderNo, TNY.RowNo, TNY.ItemID, TNY.YoteiCnt, TNY.TorikomiFile
      , case when TNJ.NyukaSum - TNY.YoteiRuikei >= 0 
          then TNY.YoteiCnt
          else 
            case when TNY.YoteiRuikei - TNJ.NyukaSum < TNY.YoteiCnt 
              then TNJ.NyukaSum + TNY.YoteiCnt - TNY.YoteiRuikei
              else 0
            end 
        end as NyukaCnt
      , case when TNJ.NyukaSum - TNY.YoteiRuikei > 0 
          then 0 
          else 
            case when TNY.YoteiRuikei - TNJ.NyukaSum < TNY.YoteiCnt 
              then TNJ.NyukaSum - TNY.YoteiRuikei
              else YoteiCnt * -1
            end 
        end AS Kabusoku 
FROM  (
        select *
             , sum(YoteiCnt) over(partition by OrderNo, ItemID order by RowNo) AS YoteiRuikei 
        from 入荷予定
      ) TNY 
      LEFT JOIN (
        select OrderNo, ItemID, sum(NyukaCnt) as NyukaSum 
        from 入荷状況
        group by OrderNo, ItemID
      ) TNJ 
      ON    TNY.OrderNo = TNJ.OrderNo 
        AND TNY.ItemID = TNJ.ItemID
order by TNY.OrderNo, TNY.RowNo

Comments

[Ryuakira]

返信が遅くなり申し訳ございません。
ありがとうございます。ご教授いただいた内容で一度検証してみます！
解決いたしましたらベストアンサーにさせていただきます！