按日期访问累计总数

本文介绍了按日期访问累计总数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要按在线日期显示我们所有能源项目的累积功率.

I need to show the cumulative power of all our energy projects by their online date.

因此，如果一个项目在 2016 年上线，那么它也在 2017 年和 2018 年上线，应该计入这些总数中.我基本上需要一张表格来回答问题

So, if a project came online in 2016, it was also online in 2017 and 2018 and should be counted in those totals. I basically need a table that answers the questions

"What was our energy capacity as of 12/31/2016, 12/31/2017,etc"

我可以通过对不同日期一遍又一遍地运行简单查询来实现，但理想情况下，我只运行一个查询并获取我需要的表.

I can do it by running a simple query over and over with different dates, but ideally I would run just one query and get the table I need.

我实际上不知道如何在 SQL 中编码，所以我只使用 Access 查询设计器，这可能会使事情变得更加困难.

I don't actually know how to code in SQL, so I just use the Access query designer, which might be making things more difficult.

得到我想要的简单查询:

Simple Query that gets what I want:

SELECT Sum(Projects.[System Size AC]) AS [SumOfSystem Size AC] FROM Projects WHERE (((Projects.[Online Date])<#1/1/2018#));

出现错误的查询:

SELECT DSum([Projects]![System Size AC],[Projects],Year([Online Date])<=[Yr]) AS [Run Sum], Year([Online Date]) AS Yr FROM Projects GROUP BY Year([Online Date]);

我可以通过对不同日期一遍又一遍地运行简单查询来实现，但理想情况下，我会得到一个表，其中包含在线日期列中的所有年份，就像当年可用的所有可用项目容量的累计总数

I can do it by running the simple query over and over with different dates, but ideally I would get one table with all the years in the online date column with like a cumulative total of all available project capacity available during that year

推荐答案

您可以使用相关子查询来做到这一点:

You can do this using a correlated subquery:

SELECT Year(p.[Online Date]) as yr, SUM(p.[System Size AC]) as this_year, (SELECT SUM(p2.[System Size AC]) FROM Projects as p2 WHERE YEAR(p2.[Online Date]) <= YEAR(p.[Online Date]) ) as running_sum FROM Projects as p GROUP BY Year([Online Date]);