根据其他列日期选择列值

时间：2023-01-04 07:55:40

I have a dataset being returned that has monthly values for different 'Goals.' The goals have unique ID's and the month/date values will always be the same for the goals. The difference is sometimes one goal doesn't have values for all the same months as the other goal because it might start at a later date, and i want to 'consolidate' the results and sum them together based on the 'First' startBalance for each goal. Example dataset would be;

我将返回一个数据集，该数据集对不同的“目标”具有月度值。“目标有唯一的ID，每个月/日的值都是相同的。”不同的是，有时一个目标与另一个目标没有相同的价值，因为它可能会在以后的日期开始，我想要“巩固”结果，并根据每个目标的“第一个”startBalance把它们加在一起。示例数据集将;

goalID    monthDate    startBalance
1         1/1/2014     10
1         2/1/2014     15
1         3/1/2014     22
1         4/1/2014     30
2         4/1/2014     13
2         5/1/2014     29

What i want to do is display these consolidated (summed) values in a table based on the 'First' (earliest Month/Year) value for each goal. The result would look like;

我要做的是根据每个目标的“第一个”(最早的月份/年)值将这些合并(汇总)值显示在一个表中。结果会是这样的;

Year        startBalance
2014        23

This is because the 'First' value for goalID of 1 is 10 and the 'First' value for goalID of 2 is '13' I am trying to ultimately use this dataset in an SSRS report through Report Builder, but the groupings are not working correctly for me so i figured if i could achieve this through my queries and just display the data that would be a viable solution.

这是因为goalID 1的‘第一次’值是10,goalID的‘第一次’值的2是‘13’我想最终在SSRS的报告通过报告生成器中使用这个数据集,但分组不正确地为我工作所以我想如果我能做到这一点通过我的查询和显示数据,将是一个可行的解决方案。

An example of real result data would be

一个真实结果数据的例子是

根据其他列日期选择列值

so i'd want the overall resultset to be;

我希望总体结果是;

Year        startBalance
2014        876266.00
2015        888319.92
2016        ---------

and so on, i understand for 2015 in that result set there is a value of 0.00 for ID 71, but usually that will contain an actual dollar amount, which would automatically adjust.

等等，我知道在2015年的结果集中ID 71的值是0。00，但通常它包含一个实际的美元金额，它会自动调整。

1 个解决方案

#1

4  

WITH balances AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY goalID ORDER BY monthDate ASC) n, startBalance, DATEPART(year, monthDate) [year]
    FROM Goals
)

SELECT [year], SUM(startBalance) startBalance
FROM balances
WHERE n = 1
GROUP BY [year]

#1

4  

WITH balances AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY goalID ORDER BY monthDate ASC) n, startBalance, DATEPART(year, monthDate) [year]
    FROM Goals
)

SELECT [year], SUM(startBalance) startBalance
FROM balances
WHERE n = 1
GROUP BY [year]
标签：

相关文章