-1

Trying to calculate values for a FIFO report. Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group, including taking only part of a row if necessary?

Request: For each Item Number, order the Purchased Items in descending order by Date, and get the maximum amount such that the sum of the Quantities is = the Amount Sold for that Item Number, taking only a part of the final row if necessary to get the quantities to match. Then output a result containing ItemNumber, NumShipments, and CostSum (which is the sum of the product of CostPer and Quantity) for the remaining stock that has not been sold.

For example, consider the following schema:

CREATE TABLE MYLIB.TOTAL_ITEM_SOLD (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , QUANTITY_SOLD DECIMAL(5, 2) NOT HIDDEN , PRIMARY KEY (ITEM_NUMBER)  ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;

CREATE TABLE MYLIB.ITEM_PURCHASE (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , DATE_ORDERED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL NOT HIDDEN , QUANTITY DECIMAL(4, 2) NOT NULL NOT HIDDEN , COST_PER DECIMAL(10, 2) NOT NULL NOT HIDDEN , PRIMARY KEY (DATE_ORDERED, ITEM_NUMBER) ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;

INSERT INTO MYLIB.TOTAL_ITEM_SOLD (ITEM_NUMBER, QUANTITY_SOLD) VALUES ('APPLE', 5);

INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 1.23);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 4, 2.34);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 5.55);

Expected result:

{ITEM_NUMBER: "APPLE", QTY_REMAINING: 3, COST_SUM: 13.44}

Because 5.55+5.55+2.34 = 13.44.

1
  • I'd asked a similar question here, but turns out the requirements had been miscommunicated. This is what's actually needed. Asking this as a separate question to avoid moving the goalpost.
    – Sarov
    Commented Aug 4, 2023 at 19:06

1 Answer 1

1

I'm not sure I understand the verbal description of the problem very well, but from what I can discern plus what your expected result seems to suggest, I think you want the following. You want to take QUANTITY_SOLD from MYLIB.ITEM and match it against the running total of QUANTITY (per ITEM_NUMBER) in MYLIB.ITEM_PURCHASE as taken in the ascending order of DATE_ORDERED. Then you want to aggregate the rows where the running total exceeds the quantity sold, adjusting QUANTITY of the first such row to reflect the total quantity subtracted.

Or, in SQL terms, something like this:

WITH
  item_purchase_with_RT AS
  ( -- get running totals of QUANTITY per ITEM_NUMBER
    SELECT
      *
    , SUM(QUANTITY) OVER (PARTITION BY ITEM_NUMBER ORDER BY DATE_ORDERED ASC)
      AS QUANTITY_RT
    FROM
      ITEM_PURCHASE
  )
, item_purchase_decreased AS
  ( -- filter out the rows where the running total is fully covered by QUANTITY_SOLD;
    -- adjust QUANTITY on those where the running total just about covers it
    SELECT
      p.ITEM_NUMBER
    , CASE
        WHEN p.QUANTITY > (p.QUANTITY_RT - s.QUANTITY_SOLD)
        THEN p.QUANTITY_RT - s.QUANTITY_SOLD
        ELSE p.QUANTITY
      END AS QUANTITY_ADJ
    , p.COST_PER
    FROM
      item_purchase_with_RT AS p
      INNER JOIN TOTAL_ITEM_SOLD AS s ON p.ITEM_NUMBER = s.ITEM_NUMBER
    WHERE
      s.QUANTITY_SOLD < p.QUANTITY_RT
  )
SELECT
  ITEM_NUMBER
, SUM(QUANTITY_ADJ) AS QTY_REMAINING
, SUM(QUANTITY_ADJ * COST_PER) AS COST_SUM
FROM
  item_purchase_decreased
GROUP BY
  ITEM_NUMBER
;

Output:

ITEM_NUMBER QTY_REMAINING COST_SUM
APPLE 3.00 13.4400

Fiddle.

Note: this solution assumes that MYLIB.TOTAL_ITEM_SOLD contains one row per ITEM_NUMBER. If that may not be the case, you can aggregate QUANTITY_SOLD per ITEM_NUMBER and use the aggregated set in the join instead of the original table.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.