SQL查询按日期计算滚动差异 [英] SQL Query to Calculate the Rolling Difference by Date

问题描述

我似乎无法完全满足需要。

I cannot seem to work this one out to be exactly what need.

我正在使用MS SQL Management Studio 2008。

I'm using MS SQL Management Studio 2008.

我有一张桌子（实际上有几个），但让它保持简单。该表包含每个项目的每日库存数据（SKU）。

I have a table (several actually) but lets keep it simple. The table contains daily stock figures for each item (SKU).

SKU DataDate               Web_qty
2   2014-11-17 00:00:00    404
2   2014-11-18 00:00:00    373
2   2014-11-19 00:00:00    1350
66  2014-11-17 00:00:00    3624
66  2014-11-18 00:00:00    3576
66  2014-11-19 00:00:00    3570
67  2014-11-17 00:00:00    9353
67  2014-11-18 00:00:00    9297
67  2014-11-19 00:00:00    9250

我只需要选择查询以返回以下内容：

I simply need the Select Query to return this:

SKU DataDate               Difference
2   2014-11-17 00:00:00    ---
2   2014-11-18 00:00:00    -31
2   2014-11-19 00:00:00    +977
66  2014-11-17 00:00:00    ---
66  2014-11-18 00:00:00    -48
66  2014-11-19 00:00:00    -6
67  2014-11-17 00:00:00    ---
67  2014-11-18 00:00:00    -56
67  2014-11-19 00:00:00    -47

我不需要---部分，我只是拥有这一点来引起人们的注意，因为它不能被计算，因为它是第一条记录。

I do not need the --- parts, I have just shown that to draw attention to the fact that this one cannot be calculated as it is the first record.

我尝试使用派生表，但是有点混乱，我需要使用一个有效的示例，以便我可以更好地理解它。

I've tried using derived tables, but its getting a little confusing, i need to play with a working example so I can understand it better.

如果有人可以将我指向正确的方向，我相信我一定能够将其他表重新合并在一起（即SKU描述和价格）。

If someone could point me in the right direction I'm sure I'll be able to join the other tables back together (i.e. SKU Description and prices).

真的很感谢大家的时间

Kev

推荐答案

尝试一下。使用相关子查询查找滚动差异

CREATE TABLE #tem
  (SKU      INT,DataDate DATETIME,Web_qty  INT)

INSERT #tem
VALUES( 2,'2014-11-17 00:00:00',404),
       (2,'2014-11-18 00:00:00',373),
       (2,'2014-11-19 00:00:00',1350),
       (66,'2014-11-17 00:00:00',3624),
       (66,'2014-11-18 00:00:00',3576),
       (66,'2014-11-19 00:00:00',3570),
       (67,'2014-11-17 00:00:00',9353),
       (67,'2014-11-18 00:00:00',9297),
       (67,'2014-11-19 00:00:00',9250)

SELECT *,
       Web_qty - (SELECT Web_qty
                  FROM   #tem a
                  WHERE  a.sku = b.SKU
                         AND a.DataDate = Dateadd(dd, -1, b.DataDate)) Roll_diff
FROM   #tem b 

这篇关于SQL查询按日期计算滚动差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！