根据最新日期信息按人数订购的每年总计

We try to calculate the running total in for each year ordered by person based on his latest date info. So i got an example for you how the data is ordered:

我们尝试根据他的最新日期信息计算每人订购的每年的运行总额。所以我得到了一个如何订购数据的例子:

Expected result:

So for each downloaded date we want to running total in of all persons ordered by year (now the year is only 2018)

因此,对于每个下载日期,我们希望按年份排序的所有人员(现在年仅为2018年)

What do we have so far:

到目前为止我们有什么:

sum(Amount)
over(partition by [Year],[Person]
order by [Enddate)
where max(Downloaded)

Any idea how to fix this?

知道如何解决这个问题吗?

2 个解决方案

#1

Just use window function

只需使用窗口功能

select *,
        sum(Amount) over (partition by Year, Downloaded) RuningTotal 
from table t

#2

Try using a subquery with a moving downloaded date range.

尝试使用具有移动下载日期范围的子查询。

SELECT
    T.*,
    RunningTotalByDate = (
        SELECT
            SUM(N.Amount)
        FROM
            YourTable AS N
        WHERE
            N.Downloaded <= T.Downloaded)
FROM
    YourTable AS T
ORDER BY
    T.Downloaded ASC,
    T.Person ASC

Or with windowed SUM(). Do no include a PARTITION BY because it will reset the sum when the partitioned by column value changes.

或者使用窗口SUM()。不包括PARTITION BY,因为当按列值分区更改时,它将重置总和。

SELECT
    T.*,
    RunningTotalByDate = SUM(T.Amount) OVER (ORDER BY T.Downloaded ASC)
FROM
    YourTable AS T
ORDER BY
    T.Downloaded ASC,
    T.Person ASC

#1