SQL Aggregrate function of Cube?
Sorry if this is the wrong forum but I am using reporting services and have decided to stick it here.
I have a set of data like this
Client ID - Year - Woodlots - Amount
234 92 - 20 $2000
234 95 - 10 $1100
300 95 10 $1100
400 99 50 $5000
543 99 50 $5000
What I need is a chart and report that shows a total of each years investors compared with a percentage of previous investors.
The first part is easy I can just use the count distinct statement and goup by Year, however the second part has me a little stump at best approach. How can I nicely get a matrix of previous years invesors as well. In the example above client 234 in the column of year 95 would count as a previous investor because he had an investment in 92.
The final data would like something like this
Year New Investors Previous Investors
92 1 0
95 1 1
99 2 0
Any advice greatly appreciated.
Cheers
Damien
