select column values based on other column date

Question

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;

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.

An example of real result data would be

enter image description here

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.

Sherman Sherman · Accepted Answer · 2014-10-09T19:35:05

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]

select column values based on other column date

1 Answers