以否定方式运行总计的SQL查询

Question

我有一张负进度的桌子

Id  ActivityID  Date   progress
1   x           Jan-10 10
2   y           Jan-10 20
3   x           Feb-10 30
4   y           Feb-10 -5
5   x           Mar-10 -10
6   y           Mar-10 25
7   x           Apr-10 30
8   y           Apr-10 20
9   x           May-10 40
10  y           May-10 40

我的目的是在没有我所做的否定的情况下获得累积：

select t1.id,t1.ActivityID,t1.Date,t1.progress,SUM(t2.progress) as sum
from tblIncrement t1
inner join tblIncrement t2
on cast('1.' + t1.date as datetime) >= cast('1.' + t2.date as datetime) and 
t1.ActivityID = t2.ActivityID
where t1.Progress > 0
group by t1.id,t1.activityID,t1.Date,t1.progress
order by t1.ActivityID,cast('1.' + t1.date as datetime)

结果：

id  ActivityID  Date   progress sum
1   x           Jan-10 10       10
3   x           Feb-10 30       40
7   x           Apr-10 30       60
9   x           May-10 40       100
2   y           Jan-10 20       20
6   y           Mar-10 25       40
8   y           Apr-10 20       60
10  y           May-10 40       100

我的最后一项任务是从这一总和中获得周期进展：

预期结果：

id  ActivityID  Date   progress sum Periodic
1   x           Jan-10 10       10  10
3   x           Feb-10 30       40  30
7   x           Apr-10 30       60  20
9   x           May-10 40       100 40
2   y           Jan-10 20       20  20
6   y           Mar-10 25       40  20
8   y           Apr-10 20       60  20
10  y           May-10 40       100 40

Answer 1

大多数数据库支持ANSI标准窗口函数，包括累积和。这使得这很容易：

select i.*,
       sum(progress) over (partition by activityid order by date) as sumeprogress
from tblIncrement i
where i.progress > 0;

我不知道什么是“定期进展”。

Answer 2

也是如此：

select t1.id,t1.ActivityID,t1.Date,t1.progress
, SUM(CASE WHEN t1.progress > 0 THEN t2.progress ELSE 0 END) as sum
, SUM(t2.progress) as Periodic
from tblIncrement t1
inner join tblIncrement t2
on cast('1.' + t1.date as datetime) >= cast('1.' + t2.date as datetime) and 
t1.ActivityID = t2.ActivityID
group by t1.id,t1.activityID,t1.Date,t1.progress
order by t1.ActivityID,cast('1.' + t1.date as datetime)

为你做这份工作吗？

编辑：

...But我的意图是从“求和”栏中得到排除负数记录后的进度。所以，从5分钟前的累积奥马尔沙欣奥萨因开始，基本上是重新产生了进步。

这是我最后的答案：

SELECT t.Id, t.ActivityID, t.Date, t.progress, t.sumprogress 
, t.sumprogress - CASE WHEN (Lag(t.ActivityID) OVER (ORDER BY t.ActivityID, t.ID)) = t.ActivityID THEN Coalesce(Lag(t.sumprogress) OVER (ORDER BY t.ActivityID, t.ID), 0) ELSE 0 END AS Periodic
FROM 
( 
   SELECT i.Id, i.ActivityID, i.Date, i.progress
   , Sum(progress) OVER (PARTITION BY activityid ORDER BY cast('1.' + i.date as DateTime)) as sumprogress 
   FROM tblIncrement i
) AS t 
WHERE Progress > 0 
ORDER BY ActivityID, cast('1.' + t.date as DateTime) 

我用你的测试数据测试了它，它似乎做得很好。