SQL Compare hourly total orders and delete current stock?

Question

Not sure if this can be done using only sql, but I would rather ask by adding another step to the process.

so let's say for example i have a set of fictitious orders

+----------+-------------+----------+----------+-----------------+-----+
| Supplier | Destination | Req_Time | Prd_Code | Prd_Description | Qty |
+----------+-------------+----------+----------+-----------------+-----+
| A        | B           | 01:00    |        1 | Chair           | 100 |
| A        | B           | 01:00    |        2 | Desk            |  50 |
| A        | B           | 01:00    |        3 | Phone           |  20 |
| A        | B           | 05:00    |        1 | Chair           | 200 |
| A        | B           | 05:00    |        2 | Desk            |  20 |
+----------+-------------+----------+----------+-----------------+-----+

And the current inventory list

+----------+----------+----------+-------+
| Supplier | Prd_Code | Prd_Desc | Stock |
+----------+----------+----------+-------+
| A        |        1 | Chair    |   150 |
| A        |        2 | Desk     |    40 |
| A        |        3 | Phone    |   100 |
+----------+----------+----------+-------+

there is a way that I can create a stored procedure (SQL 2008 -Compatible) that can subtract what I have in stock and leave me what I need

+----------+-------------+----------+----------+-----------------+-----+
| Supplier | Destination | Req_Time | Prd_Code | Prd_Description | Qty |
+----------+-------------+----------+----------+-----------------+-----+
| A        | B           | 01:00    |        2 | Desk            |  10 |
| A        | B           | 05:00    |        1 | Chair           | 150 |
| A        | B           | 05:00    |        2 | Desk            |  20 |
+----------+-------------+----------+----------+-----------------+-----+

I would like to show you what I tried, but in this case I do not know where to start.

PS I have to thank this site for formatting tables for me it is very easy
https://ozh.imtqy.com/ascii-tables/

Will appreciate any understanding and any other examples: D

Answer 1

SQL Server , SQLFiddle , , ...

WITH
    stock_changes
AS
(
    SELECT Supplier, Destination, Req_Time, Prd_Code, Prd_Description, -Qty AS Qty FROM orders
    UNION ALL
    SELECT Supplier, NULL, '00:00', Prd_Code, Prd_Desc, Stock FROM stock
),
    stock_post_order
AS
(
    SELECT
        *,
        SUM(Qty) OVER (PARTITION BY Supplier, Prd_Code
                           ORDER BY Req_Time
                               ROWS UNBOUNDED PRECEDING
                      )
                         AS new_qty
   FROM
       stock_changes
)
SELECT
    *,
    CASE WHEN new_qty > qty THEN new_qty ELSE qty END   AS order_shortfall
FROM
    stock_post_order
WHERE
    new_qty < 0

, , , .

0 ( , , , ).

, , ; ( ) . ( Stock - Order1 - Order2, etc, etc)

, .

Answer 2

, - :

SELECT
    S.Prd_Code
    ,S.Stock - Ord.OrderedQty AS RemainingStock
FROM Stock S
INNER JOIN 
(
    SELECT 
        Prd_Code
        , SUM(Qty) OrderedQty 
    FROM Orders 
    GROUP BY Prd_Code
) Ord