memymasta (11/30/2012)
Ive almost got it to work... but i fail on the joining step where i join my CTE with a derived column...
When you say you fail, what exactly does that mean? In your ON criteria, you've got...
ON myCTE.c=#a.CAST(YEAR(#a.recorddate)
change it to (take out the extra '#a.' alias, and leave the rest the same)...
ON myCTE.c=CAST(YEAR(#a.recorddate)
And it worked for me. The only differece being there are NULL's where you have zero's in your mock results. It looks like there are some better suggestions any way, but I was just curious if this was the only 'failure' you were experiencing.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.