SQL 查询清单账龄 - 消除数据行，直到 SUM=另一个字段

Question

我正在寻找创建库存账龄报告。我将根据每个部件收到的数量从库存交易表中提取数据。我需要我的 SQL 查询只返回我们有库存的数据行，例如，如果我在 1 月 1 日收到第 10 部分 #12347，在 3 月 1 日收到 5 个，如果我只剩下 5 个库存，那么我只需要返回 3 月 1 日的数据行。

这是我用来提取数据的代码：

SELECT intran.fdate AS 'RCV Date', intran.fpartno AS 'Part #', intran.fqty AS 'RCV QTY',
       inmast.fonhand as 'On Hand'
FROM intran
INNER JOIN inmast
    ON  intran.fpartno = inmast.fpartno
    AND intran.fcpartrev = inmast.frev
WHERE (intran.fpartno = '12347')
  AND (intran.ftype = 'R')
ORDER BY 'RCV Date' DESC

结果

RCV Date               Part # RCV QTY   On Hand
2017-04-03 00:00:00.000 12347 1.00000   2.00000 
2017-01-28 00:00:00.000 12347 1.00000   2.00000 
2016-11-28 00:00:00.000 12347 2.00000   2.00000 
2016-10-28 00:00:00.000 12347 2.00000   2.00000 
2016-08-15 00:00:00.000 12347 1.00000   2.00000

期望的结果：

RCV Date               Part # RCV QTY   On Hand
2017-04-03 00:00:00.000 12347 1.00000   2.00000 
2017-01-28 00:00:00.000 12347 1.00000   2.00000

因为我手头的数量为 2，所以我只想查看 RCV 数量总和 = 2 的最新行。

提取此数据后，我会将其导出到 Excel 数据透视表中以显示每个部分的年龄。

提前感谢大家的帮助。

Answer 1

让我们从将 OnHand 值保留在变量中开始：

declare @OnHand int = (select inmast.fonhand
                       FROM intran
                       INNER JOIN inmast
                       ON  intran.fpartno = inmast.fpartno
                       AND intran.fcpartrev = inmast.frev)

然后，我们需要找出我们需要在输出中返回多少行。为此，使用如下所示的 Row_number(( 函数将非常有用。@counter值用于确定我们需要返回的行数。

declare @counter int = 1, @sumValue int = 0;
while @sumValue <= @checkValue
begin
   set @counter = @counter + 1;
   set @sumValue = @sumValue + (select intran.fqty from
                                (
                                  select * from (
                                                 select row_number() over (order by id fdate ASC) as rownumber, 
                                                 fdate, fqty from intran)
                                                ) x
                                  where rownumber = @counter
                                 ) t
                               )
end

然后，我们只需要使用 @counter 来打印结果集。

declare @sql nvarchar(255) = 'select top '+cast(@counter as varchar(5))+' * FROM intran
                              INNER JOIN inmast
                              ON  intran.fpartno = inmast.fpartno
                              AND intran.fcpartrev = inmast.frev)'
EXECUTE sp_executesql @sql, N'@counter int', @counter = @counter;