Returning multiple months of data into one select

Question

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.

score 0 · Answer 1 · answered Jul 11 '12 at 15:47

SELECT  [C].[name_1] AS [APPLICATION]
        ,COUNT([name_1]) AS [CALL COUNT]
        ,[l].[location_ref]
        ,[dbo].[ufn_GetDateTime_CalenderYearMonth]([resolve_time]) AS [StartCalenderYearMonth]
FROM  [call_logging] [C] INNER JOIN [problem_type] [t] 
                                    ON [t].[ref_composite] = [c].[ref_composite] 
                                    AND [c].[resolve_group] IN ('48', '60')
                         INNER JOIN [location] [l] ON [c].[location_ref] = [l].[location_ref]
WHERE [C].[resolve_time] BETWEEN '2011-01-01' AND GETDATE()
GROUP BY [C].[name_1], [l].[location_ref], [dbo].[ufn_GetDateTime_CalenderYearMonth]([resolve_time])

And the code for ufn_GetDateTime_CalenderYearMonth is:

CREATE FUNCTION [dbo].[ufn_GetDateTime_CalenderYearMonth] (@DateTime datetime)    
 RETURNS varchar(20)    
AS    
BEGIN    
 declare @dateString varchar(20)    
 declare @yearString varchar(10)    
 declare @monthString varchar(10)    

 set @yearString = cast( DATEPART(year, @DateTime) as varchar(10))    

 if(DATEPART(month, @DateTime) < 10)    
  set @monthString = '0' + cast( DATEPART(month, @DateTime) as varchar(5) )    
 else    
  set @monthString = cast( DATEPART(month, @DateTime) as varchar(5) )    

 set @dateString = @yearString + '-' +  @monthString    

 RETURN (@dateString)    

END

You just slap the resultset in a matrix and group everything by [StartCalenderYearMonth] and it will show numbers for each month from 1st of Jan 2011 till now..

Returning multiple months of data into one select

1 Answers1