L Riley L Riley - 3 months ago 9
SQL Question

SQL Compare Hourly Total Orders and Remove Current Stock Possible?

Not sure if this can be done using sql alone but I'd rather ask then add another step to a process.

so say for example I have a set of dummy 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 a current stock list

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


is there a way I can produce a Stored Procedure (SQL 2008 -Compatible) that can subtract what I do have in stock and leave me with what I require like so

+----------+-------------+----------+----------+-----------------+-----+
| 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'd like to show you what I have attempted but in this case I wouldn't know where to start.

P.S
Have to thank this site for formatting the tables for me very easily
https://ozh.github.io/ascii-tables/

Will Appreciate any insight and any examples even more :D

Answer

I don't have SQL Server to hand and SQLFiddle isn't playing nicely with me lately, so this is untested, but the logic should work...

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

First invert your order quantities to be negative, so they are the amount the stock levels are going to change by.

Next, union your stock levels to the orders, with a required time of 0 (to make it a bit like an order that delivers stock rather than takes stock, and come before all your other orders).

Next, work out what the total remaining quantity of a product is after an order; by summing up all the preceding rows (in time order) for that product. (Giving Stock - Order1 - Order2, etc, etc)

Then select the rows where the new stock level has gone negative.