WITH CTEPBMTickets AS    
   (  
   SELECT ContactReason,  
       COUNT(DISTINCT ProblemFolderName) AS ProblemCount,  
       --1/0 AS ProblemCount,--Test for Error Logging  
       RIGHT(CONVERT(VARCHAR(12),FC.FiscalMonth),3)+'-'+  Right(CONVERT(VARCHAR(4),FC.FiscalMonth),2) AS Datemonth,  
       ROW_NUMBER() OVER(PARTITION BY ContactReason ORDER BY FC.FiscalMonth DESC) AS 'RowNumber',  
       CAST(SUBSTRING(FC.FiscalMonth, CHARINDEX('/', FC.FiscalMonth) + 1, 2) AS INT) AS Month_Number,  
       FC.FiscalMonth AS FiscalMonthOrder  
    FROM  reporting.Foundation_ProblemTickets (NOLOCK)  
		LEFT JOIN  reporting.Foundation_ReferenceLinks RL (NOLOCK)
	ON ProblemFolderID = RL.TicketFolderID
		INNER JOIN reporting.Foundation_FiscalCalendar AS FC (NOLOCK)  
    ON CONVERT(varchar,FC.CalendarDate,112) = convert(varchar,CreatedDateTime,112)  
    WHERE --[status] <>('Investigation & Diagnosis,Known Error,Resolved,closed')  
        CreatedDateTime BETWEEN (SELECT DBO.GetRollingFiscalMonthStartDate(12)) AND (SELECT DBO.GetRollingFiscalMonthEndDate(12))  
        AND ProblemCurrentStage NOT IN ('Abandoned','Cancelled')  
        AND (NULLIF(@Group,'All') IS NULL OR OrganizationGroupName = @Group)  
     AND (NULLIF(@SubGroup,'All') IS NULL OR OrganizationSubGroupName = @SubGroup)  
        AND (NULLIF(@Team,'All') IS NULL OR CONVERT(nvarchar(MAX),ProblemTeam) = @Team)  
		AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@Status,'^@!')) = 0 OR ISNULL([Status],'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@Status,'^@!')))  
        AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@Priority,'^@!')) = 0 OR ISNULL(CONVERT(nvarchar(MAX),Priority),'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@Priority,'^@!')))  
        AND ((NULLIF(@ProblemCategory,'All') IS NULL OR  ISNULL(Category,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@ProblemCategory,'^@!'))))  
        AND ((NULLIF(@ProblemSubCategory,'All') IS NULL OR  ISNULL(Subcategory,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@ProblemSubCategory,'^@!'))))  
        AND ((NULLIF(@RootCauseCategory,'All') IS NULL OR  ISNULL(KnownErrorCategory,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@RootCauseCategory,'^@!'))))  
        AND ((NULLIF(@RootCauseSubcategory,'All') IS NULL OR  ISNULL(KnownErrorSubcategory,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@RootCauseSubcategory,'^@!'))))  
        AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@ServiceAffected,'^@!')) = 0 OR ISNULL(ServiceAffectedCIUniqueID,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@ServiceAffected,'^@!')))  
        AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@ContactReason,'^@!')) = 0 OR ISNULL(ContactReason,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@ContactReason,'^@!')))  
        AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@KnownErrorFix,'^@!')) = 0 OR ISNULL(KnownErrorType,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@KnownErrorFix,'^@!')))  
        AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@ContactReasonType,'^@!')) = 0 OR ISNULL(ContactReasonType,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@ContactReasonType,'^@!')))  
        AND ((SELECT COUNT(*) FROM dbo.fn_ConvertStringToList(@LinkedModule,'^@!')) = 0 OR ISNULL(RL.RelatedModuleType,'(null)') IN (SELECT Value FROM dbo.fn_ConvertStringToList(@LinkedModule,'^@!'))OR (RL.RelatedModuleType IS NULL))  
  
    GROUP BY    ContactReason,  
       FC.FiscalMonth)  
    
     SELECT    T1.ContactReason,  
      T1.Datemonth,  
      T1.ProblemCount,  
      T1.Month_Number,  
      T1.FiscalMonthOrder,  
      CONVERT(NUMERIC(20,2),ISNULL(((t1.ProblemCount-t2.ProblemCount)/CAST(t2.ProblemCount AS FLOAT)) * 100,''))  AS Change  
	FROM CTEPBMTickets T1   
		LEFT JOIN  CTEPBMTickets T2  
	ON T1.ContactReason = T2.ContactReason  
		AND (T1.RowNumber) = (T2.RowNumber)-1