sql - 每天按 SKU 运行总和

Question

我目前一直在使用此代码，试图检索我们每天持有的任何产品的总成本美元（库存对账）：

SELECT dates.Date,
  COALESCE(ledger.Cost,0) as Cost,
  SKU,
  Description,
  SUM(COALESCE(Cost,0)) OVER (PARTITION BY SKU ORDER BY Date)
  from dates
  LEFT JOIN ledger ON ledger.[Posting Date] = dates.Date

我确实加入了带有日期维度的分类帐表，以便在输出中包含所有日期（即：分类帐在日历中每天都没有交易）。挑战是我希望 SKU 按天持有的总成本美元，即使在该特定日历日期的分类帐表中没有交易。（例如：我正在尝试获取我们今天为每个 SKU 持有的价值多少美元的产品，今天显示的唯一 SKU 是今天已发布交易的 SKU。而其他的 SKU 显示但在其最后发布日期账本中的交易）

这是 SQL 语句的结果示例：

    Date    Cost          SKU   Desc    Running Sum
2/7/2020    $27,451.38    1     a   $11,915,183.80 
2/6/2020    $109,784.62   1     a   $11,805,399.18 
2/5/2020    $179,860.36   1     a   $11,625,538.82 
2/5/2020    ($3,713.73)   2     b   $373,587.93 
2/4/2020    $106,539.44   1     a   $11,518,999.38 
2/4/2020    ($3,713.73)   2     b   $377,301.66 
2/3/2020    ($104,303.18) 1     a   $11,623,302.56 

由于 2 月 7 日第 2 项没有交易，因此它不会显示在该特定日期的查询中。我还想在 2 月 7 日为项目 #2 设置一行，显示其在最近交易日期（即 2 月 5 日，373,587.93 美元）持有的总金额

标签： sqlsql-server

Answer 1

这是一种使用方法apply：

with q as (
      SELECT d.Date, COALESCE(l.Cost, 0) as Cost, SKU, Description,
             SUM(COALESCE(Cost,0)) OVER (PARTITION BY SKU ORDER BY Date) as sumcost,
            ROW_NUMBER() OVER (PARTITION BY Sku, Description ORDER BY d.Date DESC) as seqnum
      FROM dates d LEFT JOIN
           ledger l
           ON l.[Posting Date] = d.Date
     )
select v.dte, q.cost, q.SKU, q.description, q.sumcost
from q cross join
     (select max(date) as maxd from dates) dmax cross apply
     (values (1, q.date), (2, dmax.maxd)) v(which, dte)
where v.dte < dmax.maxd or
      (which = 2 and seqnum = 1);

但是，如果您想要每个分类帐的所有缺失日期，那么这更简单：

  SELECT d.Date, COALESCE(l.Cost, 0) as Cost, SKU, Description,
         SUM(COALESCE(Cost,0)) OVER (PARTITION BY SKU ORDER BY Date) as sumcost
  FROM dates d CROSS JOIN
       (SELECT DISTINCT SKU, Description FROM ledger
       ) sd LEFT JOIN
       ledger l
       ON l.[Posting Date] = d.Date AND
          l.SKU = sd.SKU AND
          l.Description = sd.Description ;