SQL 比较每小时总订单并删除当前库存可能吗? [英] SQL Compare Hourly Total Orders and Remove Current Stock Possible?

问题描述

不确定这是否可以单独使用 sql 来完成，但我宁愿询问然后在流程中添加另一个步骤.

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 |
+----------+-------------+----------+----------+-----------------+-----+

以及当前的股票清单

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

有没有一种方法可以生成一个存储过程(SQL 2008 兼容)，它可以减去我现有的库存，然后留下我需要的东西

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.

附言不得不感谢这个网站为我轻松格式化表格
https://ozh.github.io/ascii-tables/

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

将更加欣赏任何见解和任何示例:D

Will Appreciate any insight and any examples even more :D

推荐答案

我手头没有 SQL Server 并且 SQLFiddle 最近不太适合我，所以这是未经测试的，但逻辑应该可以工作...

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.

接下来，将您的库存水平与订单结合起来，所需时间为 0 (使其有点像交付库存而不是接受库存的订单，并且排在所有其他订单之前).

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).

接下来，计算下订单后产品的总剩余数量；通过总结该产品的所有前面的行(按时间顺序).(提供 Stock - Order1 - Order2 等)

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.

这篇关于SQL 比较每小时总订单并删除当前库存可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！