一种方法是使用库存和销售的累积总和创建区间，然后根据重叠进行连接：

WITH stock_intervals AS (
    SELECT *,
        SUM(Qty) OVER w - Qty AS IntervalStart,
        SUM(Qty) OVER w AS IntervalEnd
    FROM stock
    WINDOW w AS (PARTITION BY ID ORDER BY DatePurchased)
),
sales_intervals AS (
    SELECT *,
        SUM(Qty) OVER w - Qty AS IntervalStart,
        SUM(Qty) OVER w AS IntervalEnd
    FROM sales
    WINDOW w AS (PARTITION BY ID ORDER BY Dt)
)
SELECT s.ID, s.DatePurchased, s.Qty, s.Price,
    LEAST(d.IntervalEnd, s.IntervalEnd) - GREATEST(d.IntervalStart, s.IntervalStart) AS tQty,
    s.Price * (LEAST(d.IntervalEnd, s.IntervalEnd) - GREATEST(d.IntervalStart, s.IntervalStart)) AS tPrice
FROM stock_intervals s
JOIN sales_intervals d
  ON s.ID = d.ID
  AND s.IntervalStart < d.IntervalEnd
  AND d.IntervalStart < s.IntervalEnd
ORDER BY s.ID, s.DatePurchased;

字符串
注意事项：我将Dt列添加到sales中，这样就可以对累计和进行排序。
这里有一个db<>fiddle。

如果你想知道每个条目的总价格：

SELECT date, qty, sold_qty*price AS total FROM <joined_table> WHERE id = <id> ORDER BY date ASC;

字符串
如果您想要项目的总收入：

SELECT id, MIN(qty), SUM(sold_qty * price) AS total
FROM joined_table
GROUP BY id;

型
假设你的数量总是下降，如果你想要最新条目的数量，你应该使用子查询。
您应该将用于连接表的代码替换为joined_table