sql – 窗口函数 – 行使重置运行总计

我正在行使SQL Server 2012来构建库存打算/从头订购引擎.

我有一堆过期的买卖营业,称之为名誉和借记.我想一次做两件事：

>天生运行总计(逐日净余额)
>天生增补提议.增补将重置运行
总计(#1)回零.

该表如下所示：

CREATE TABLE TX (TDate DATETIME,Qty   INT);

INSERT INTO TX VALUES ('2014-03-01',20);  
INSERT INTO TX VALUES ('2014-03-02',-10); 
INSERT INTO TX VALUES ('2014-03-03',-20); 
INSERT INTO TX VALUES ('2014-03-04',-10); 
INSERT INTO TX VALUES ('2014-03-05',30); 
INSERT INTO TX VALUES ('2014-03-06',-20);  
INSERT INTO TX VALUES ('2014-03-07',10);  
INSERT INTO TX VALUES ('2014-03-08',-20); 
INSERT INTO TX VALUES ('2014-03-09',-5);

我正在行使SQL 2012 SUM OVER()窗口函数来表现这些的运行总计.

select TDate,Qty,RunningTotal,RecommendedReplenish from (
    select 
        TDate,SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)  as RunningTotal,-1 * (CASE WHEN Qty < 0 AND SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED     PRECEDING) < 0 
                THEN 
            CASE WHEN Qty >  SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)     THEN Qty ELSE SUM(Qty) OVER (ORDER                        BY TDate ROWS UNBOUNDED PRECEDING) END
        ELSE 0 END) as RecommendedReplenish
        /* Wrong,does not account for balance resetting to zero */
    from TX 
) T order by TDate

假如它低于零,我必要找到一种要领将运行总计(也就是RT)重置为零.

我的查询,个中Qty和RT均为负数,并将这些数据作为第一次保举增补的更大(更少负面).这是第一次正常事变.

我不知道怎样从窗口运行总计中扣除这个…假如也许的话,我想在单个语句中执行此操纵.

以下是我要求的输出择要：

TDate        Qty    R.Tot  Replenish     New RT
-----------  ----   -----  -----------  ---------
3/1/2014     20      20                    20
3/2/2014    -10      10                    10
3/3/2014    -20     -10       10            0
3/4/2014    -10     -20       10            0
3/5/2014     30      10                    30
3/6/2014    -20     -10                    10
3/7/2014     10       0                    20
3/8/2014    -20     -20                     0
3/9/2014    - 5     -25        5            0

Itzik Ben-Gan,Joe Celko或其他SQL好汉,你在表面吗？

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!