Michael Valentine Jones (12/3/2012)
A calendar table is not really needed for this:
select
YearMonth = dateadd(mm,datediff(mm,0,[recorddate]),0),
MyValueSum = sum([myvalue])
from
#a
group by
dateadd(mm,datediff(mm,0,[recorddate]),0)
order by
dateadd(mm,datediff(mm,0,[recorddate]),0)
Results:
YearMonth MyValueSum----------------------- -----------
2012-01-01 00:00:00.000 30
2012-02-01 00:00:00.000 70
2012-08-01 00:00:00.000 50
2012-12-01 00:00:00.000 130
The op would also like "0" for the intervening months so we have to figure out some way to accomplish that. a calendar table to join to seems to be the easiest from what i can see.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]