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

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，则当前情况的输出为。product_id：1OUT_OF_STOCK_DATE：2019-03-4ZERO_STOCK_DAYS：2因为在2019-03-6的商品中购买并已经有库存

product_id：2OUT_OF_STOCK_DATE：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