Returning multiple months of data into one select
===

I have a question on SQL 2008 which is probably quite easy but I can't see the woods for the trees now.  

I am trying to produce a sql based report detailing the last six months of helpdesk issue stats, per application, per office, per month which I then take into ssrs to apply prettiness :o)

Anyway - I have my script, which is fine on a month by month basis, for example;

    SELECT      distinct t.name_1 'Application', 
               (select distinct name from location where location_ref = c.location_ref) as office,
	           Count (t.name_1) as [Call Count], 
               datename(month, dateadd(month,-2,getdate()))+' '+datename(year, dateadd(month,-2,getdate())) as [Report Month]

    FROM        call_logging C 
          		Inner Join problem_type t On t.ref_composite = c.ref_composite 
  			
    AND         c.resolve_time between onvert(datetime,convert(varchar,month(dateadd(m,-2,getdate()))) + '/01/' + convert(varchar,year(dateadd(m,-2,getdate()))))
	       		and	convert(datetime,convert(varchar,month(dateadd(m,-1,getdate()))) + '/01/' + convert(varchar,year(getdate()))) 
                and c.resolve_group in ('48', '60')

which brings back all of May's issues.

The problem is that t.name_1 (the application in which the issue is for) is dynamic and the list grows or shrinks every month.

I basically need a layout of

APPLICATION   OFFICE   COUNT   JUNE  MAY APRIL MARCH FEB JAN

WORD          LONDON   20      1     1   2     5     10  1

WORD          PARIS    10      2     3   1     2     0   3

EXCEL         MADRID   05      0     0   3     2     0   0

etc (if that makes sense on this layout!)

I've gone down the 6 separate reports road but it just doesn't look very nice in ssrs.  I've thought about #tmptables but they don't like inserting distinct rows. 

Any ideas would be gratefully received.  I have very little hair as it is and what I have is at risk!




