split a row value according to a specific column



  • I have total value in table A and need to get row value in table B until reached value in table A.

    Table A

    ID Available
    1 99
    2 0

    Table B

    ID Rack RequiredQty
    1 A 60
    1 B 102
    1 C 8
    2 A 10

    Expected Results

    ID Rack RequiredQty FulfilledQty
    1 A 60 60
    1 B 102 39
    1 C 8 0
    2 A 10 0


  • You can use a recursive CTE to produce a running total and subtract that from the required quantity:

    ;WITH CTE AS (
      SELECT ID, Rack, Required
        , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Rack ASC) AS Rn
      FROM TableB
    ), CTE2 AS (
      SELECT b.ID
        , b.Rn
        , b.Rack
        , b.Required
        , CASE
            WHEN b.Required > a.Available THEN a.Available
            ELSE a.Available - (a.Available - b.Required) 
          END AS Fulfilled
        , CASE
            WHEN b.Required > a.Available THEN 0
            ELSE a.Available - (a.Available - (a.Available - b.Required))
          END AS Available
      FROM CTE b
      INNER JOIN TableA a ON a.ID = b.ID
      WHERE b.Rn = 1
      UNION ALL
      SELECT b.ID
        , b.Rn
        , b.Rack
        , b.Required
        , CASE
            WHEN b.Required > a.Available THEN a.Available
            ELSE a.Available - (a.Available - b.Required) 
          END AS Fulfilled
        , CASE
            WHEN b.Required > a.Available THEN 0
            ELSE a.Available - (a.Available - (a.Available - b.Required))
          END AS Available
      FROM CTE b
      INNER JOIN CTE2 a ON a.ID = b.ID AND a.Rn = b.Rn-1
      WHERE b.Rn > 1
    )
    

    SELECT ID
    , Required AS RequiredQty
    , Fulfilled AS FulfilledQty
    FROM CTE2
    ORDER BY ID, Rack

    You can see a working example in this https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6babb8eb2cb722b0e22bb1107a4d5d23 , which produces the below output:

    ID Rack RequiredQty FulfilledQty
    1 A 60 60
    1 B 102 39
    1 C 8 0
    2 A 10 0



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2