sql-server - 从库存表中，找出库存为零的两个日期之间的日期

Question

我有一个 SQL 表 https://pasteboard.co/IfFt5rF.png其中有数千条关于产品流的记录，如果产品被出售，数量为负，当产品被购买时，数量为正。 product_id 是每个产品的唯一标识符。我想输入日期并找出该日期之间的每个月，当时库存为零。

我想首先通过找出过去期间的总和来计算每个月的起始余额，然后逐行添加新数据，如果它为零，那么它是一个日期，但这种逻辑看起来非常糟糕，甚至不知道如何在 SQL 中处理它。

我使用 Microsoft SQL 2014

declare @Table table (general_id bigint, date datetime, product_id bigint, quantity float, 
price float, code nvarchar(max), name nvarchar(max), partnumber nvarchar(max), 
description nvarchar(max), rate float, comment nvarchar(max), currency nvarchar(max), waybill nvarchar(max))

insert into @Table (general_id, date, product_id, quantity, price, code, name, partnumber, description, rate, comment, currency, waybill)
  select 1, '2019-03-1 16:33:00', 1, 10, 100, 101010, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'
  union all
  select 2, '2019-03-2 16:33:09', 1, -5, 100, 101010, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'
  union all
  select 3, '2019-03-3 16:33:12', 1, -3, 100, 101010, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'
  union all
  select 4, '2019-03-4 16:39:00', 1, -2, 100, 101010, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'
  union all
  select 5, '2019-03-4 16:39:41', 2, 40, 100, 102020, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'
  union all
  select 6, '2019-03-5 16:39:00', 2, -40, 100, 202020, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'
  union all
  select 7, '2019-03-6 16:39:00', 1, 25, 100, 101010, 'test', 'testnumber', 'testdescription', 1.0, 'testcomment', 'USD', 'nobill'


SELECT DISTINCT product_id, code, name, partnumber, SUM(quantity) 
FROM @TABLE
GROUP BY product_id, code, name, partnumber 
ORDER BY product_id ASC                                 

如果输入的日期范围为 2019-03-01 到 2019-03-31，则当前案例的输出将为。产品编号:1缺货日期:2019-03-4zero_stock_days:2 因为在 2019-03-6 购买了商品并且已经有货

产品编号:2缺货日期:2019-03-5zero_stock_days:26 因为它再也没有买过

Answer 1

使用 Sql Server >2008 中可用的窗口函数尝试此查询 :)

SELECT [lagDate] outOfStockStart,
       [date] outOfStockEnd, 
       product_id, 
       daysOutOfStock
FROM (
    SELECT *, 
           CASE WHEN LAG(stock) OVER (PARTITION BY product_id ORDER BY [date]) = 0 AND stock > 0 THEN
             DATEDIFF(day, lagDate, [date]) ELSE NULL END daysOutOfStock
    FROM (
        SELECT [date],
               LAG([date]) OVER (PARTITION BY product_id ORDER BY [date]) lagDate,
               product_id,
               SUM(quantity) over (PARTITION BY product_id ORDER BY [date]) stock
        FROM @TABLE
    ) a
) a WHERE daysOutOfStock IS NOT NULL