获取按周分组的列中的最大运行总和 [英] Get max of a running sum in a column grouped by week

问题描述

Hy，



情况：

我有一张仓库物品移动表。主要列是ID，Date，ItemID，Pallets，PFactor等。



必须计算的列：

- MaxWeekCost - 每周max（runingSum（托盘* pfactor））





一周内的物品（无关紧要）可以有价值：

4,4

5,9

6,15

1， 16

-2,14

1,15

-3,12


在右侧是BOLD最大值的运行总和。



我需要计算每周的值（如16）一年。



我代表从tableadapter加载的rdlc报告中的数据，所以如果我在SQL语句中计算它并不重要tableadapter或在rdlc报告中。



Thx。

Hy,

The situation:
I have a table for warehouse item movements. The main columns are ID,Date,ItemID,Pallets,PFactor etc.

The columns that has to be calculated:
- MaxWeekCost - max (runingSum(pallets*pfactor)) for each week


In a week the items (it doesn't matter what) can have values:
4 , 4
5, 9
6 , 15
1, 16
-2, 14
1 , 15
-3 , 12

On the right side is the running sum with the BOLD max value.

I need to calculate the value (like 16) for each week in a year.

I´m representing the data in a rdlc report that is loaded from a tableadapter so it doesn't matter for me if i calculate it in a SQL statement of the tableadapter or in the rdlc report.

Thx.

推荐答案

阅读以下内容： http://stackoverflow.com/questions/14953294/how-to-get-running-sum-of-a-column-in-sql-server [ ^ ]

Read the following : http://stackoverflow.com/questions/14953294/how-to-get-running-sum-of-a-column-in-sql-server[^]

看看例子：

Have a look at example:

DECLARE @tmp TABLE(ID INT IDENTITY(1,1), aDATE DATETIME, Pallets INT)

INSERT INTO @tmp (aDATE, Pallets)
VALUES('2014-09-29', 4),('2014-09-30', 5),('2014-10-01', 6),('2014-10-02', 1),('2014-10-06', -2),('2014-10-07', 1),('2014-10-08', -3)

SELECT t2.ID, t2.aDATE, DATEPART(wk, t2.aDATE) AS aWeek, SUM(t1.Pallets) AS RunningSum
FROM @tmp AS t1 INNER JOIN (
    SELECT ID, aDATE, DATEPART(wk, aDATE) AS aWeek, Pallets
    FROM @tmp
    ) AS t2 ON t1.ID <= t2.ID
GROUP BY t2.ID, t2.aDATE, DATEPART(wk, t2.aDATE)
ORDER BY t2.ID

结果：

Result:

ID   aDATE                     aWeek   RunningSum
1   2014-09-29 00:00:00.000    40     4
2   2014-09-30 00:00:00.000    40     9
3   2014-10-01 00:00:00.000    40     15
4   2014-10-02 00:00:00.000    40     16
5   2014-10-06 00:00:00.000    41     14
6   2014-10-07 00:00:00.000    41     15
7   2014-10-08 00:00:00.000    41     12

现在，您需要定义字段rdlc报告，应将数据分组。在这种情况下，它是 aWeek 。

with tt

as

（

选择评价者，ROW_NUMBER（）结束（按评价者排序）作为[Rowp]来自be_Posts

）

选择评价者，（选择总和（评价者来自tt，其中rowp< = t.Rowp）来自tt t

with tt
as
(
select Raters,ROW_NUMBER() over (order by Raters) as [Rowp] from be_Posts
)
select Raters,(select Sum(Raters) from tt where rowp<=t.Rowp) from tt t

这篇关于获取按周分组的列中的最大运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！