我需要按在线日期显示我们所有能源项目的累积功率.
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]);更多推荐
按日期访问累计总数
发布评论