Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


	
CREATE PROCEDURE [dbo].[ReportingServices_PBM_TrendFor_TotalSummation_ContactReason]
  /***************************************************************************  
 Procedure  : [dbo].[ReportingServices_PBM_TrendFor_TotalSummation_ContactReason]  
 Description : This procedure used for Trend Report for Problem Contact Reason  
 Created by  : KIRANKUMAR.J (v-kiranj)  
 Created on  : 2010-10-20  
 Modified by : Rajasekhar Boggarapu (v-Rajbog)  
 Modified on : 2011-03-28   
 Modification: Modifiying the Sp for Resolving the Bug 953770,953766.  
 Example     :--exec [dbo].[ReportingServices_PBM_TrendFor_TotalSummation_ContactReason] 'All','All','Test reason-2,Test reason-1'  
 ****************************************************************************/ 
( 
	 @User					NVARCHAR(200) = NULL,  
	 @ReportName			VARCHAR(MAX) = NULL,  
	 @Group					VARCHAR(MAX) = NULL,  
	 @SubGroup				VARCHAR(MAX) = NULL,  
	 @Team					VARCHAR(MAX) = NULL,  
	 @ContactReason			VARCHAR(MAX) = NULL,  
	 @Status				VARCHAR(MAX) = NULL,  
	 @Priority				VARCHAR(MAX) = NULL,  
	 @ProblemCategory		VARCHAR(MAX) = NULL,  
	 @ProblemSubCategory	VARCHAR(MAX) = NULL,  
	 @ServiceAffected		VARCHAR(MAX) = NULL,  
	 @RootCauseCategory		VARCHAR(MAX) = NULL,  
	 @RootCauseSubCategory	VARCHAR(MAX) = NULL,  
	 @ContactReasonType		VARCHAR(MAX) = NULL,  
	 @KnownErrorFix			VARCHAR(MAX) = NULL,  
	 @LinkedModule			VARCHAR(MAX) = NULL,  
	 @ProcessingID			NVARCHAR(100) = NULL
 )  
AS    
BEGIN    
SET NOCOUNT ON;  
SET ANSI_NULLS ON;

 -- Global Variables  
 DECLARE @Out_ExecutionID NUMERIC,  
   @ErrorProcedure  NVARCHAR(200),   
   @ErrorMessage	NVARCHAR(MAX),   
   @ErrorLine		NUMERIC,    
   @ErrorSeverity	NUMERIC,    
   @ErrorState		NUMERIC  
     
  BEGIN TRY  
  
    DECLARE @StartTime DATETIME,@Duration BIGINT, @RowsReturned INT  
    SET @StartTime = GETUTCDATE()  
      
    DECLARE @ExecutionCriteria VARCHAR(MAX)  
    SET @ExecutionCriteria = @User		+'��'+  
           @ReportName					+'��'+  
           @Group						+'��'+  
           @SubGroup					+'��'+  
           @Team						+'��'+  
           @ContactReason				+'��'+  
           @Status						+'��'+  
           @Priority					+'��'+  
           @ProblemCategory				+'��'+  
           @ProblemSubCategory			+'��'+  
           @ServiceAffected				+'��'+  
           @RootCauseCategory			+'��'+   
           @RootCauseSubCategory		+'��'+            
           @ContactReasonType			+'��'+  
           @KnownErrorFix				+'��'+  
           @LinkedModule   				+'��'+           
           @ProcessingID   				+'';  
                   
    -- PREPARING XML STRING FOR EXECUTION CRITERIA  
    SELECT @ExecutionCriteria = dbo.GetXMLParameterStringForExecutionCriteria(OBJECT_NAME(@@PROCID), @ExecutionCriteria)  
      
  ;WITH CTEPBMTickets AS    
  (  
	SELECT 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 (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 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   FC.FiscalMonth)  
   SELECT     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.RowNumber) = (T2.RowNumber)-1  
      
    SET @RowsReturned   = @@ROWCOUNT;   
  SET @Duration  = DATEDIFF(MS,@StartTime,GETUTCDATE())   
      
     
  -- INSERT THE STATISTICS INFO FOR DATA UTILIZATION REPORT  
  EXEC ReportingServices_DataUtilization_InsertUtilizationData @ProcessingID,@User, @ExecutionCriteria, @StartTime, @Duration, @RowsReturned, @ReportName, @Out_ExecutionID OUT  
  
  END TRY  
    
  BEGIN CATCH  
    -- INSERT THE STATISTICS INFO FOR DATA UTILIZATION REPORT    
    EXEC ReportingServices_DataUtilization_InsertUtilizationData @ProcessingID, @User, @ExecutionCriteria, @StartTime, 0, 0, @ReportName, @Out_ExecutionID OUT  
      
    -- LOGGING ERROR INFO  
    SELECT  @ErrorProcedure		= ERROR_PROCEDURE(),   
			  @ErrorMessage		= ERROR_MESSAGE(),   
			  @ErrorLine		= ERROR_LINE(),   
			  @ErrorSeverity	= ERROR_SEVERITY(),   
			  @ErrorState		= ERROR_STATE()  
    EXEC [DBO].[ReportingServices_ErrorLog_InsertErrorInfo] @ProcessingID, @User, @ErrorProcedure, @ErrorMessage, @ErrorLine, @ErrorSeverity, @ErrorState, @Out_ExecutionID  
  
  END CATCH  
END