--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 27
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 

-- This query is to find the Clients that are not protected in last 30 days.
-- This is used for SLA report in health check.

DECLARE @LastNDays int =30
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting') OR (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        


-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
  
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
	if(@groupId > 0)
	BEGIN
		insert into @includeGroups values(@groupId);
		DECLARE @tmpStr NVARCHAR(100);
		SELECT @tmpStr = NAME FROM APP_ClientGroup WHERE id = @groupId;
		IF(@tmpStr IS NOT NULL)
			SET @groupStrName = @groupStrName + ', ' + @tmpStr
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0

IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
	SET @specialGroupExists =1


DECLARE @csTimeZone NVARCHAR(1024) = ''
SELECT	@csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
	SELECT	@csTimeZone = timeZone FROM APP_CommCell WHERE id = 2

	SELECT	@csTimeZone = TimeZoneStdName FROM SchedTimeZone
	WHERE	TimeZoneName= SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END

DECLARE @nProtected		INT = 0
DECLARE @nNotProtected	INT = 0
DECLARE @nExcluded		INT = 0
DECLARE @MultiDaySLA	INT = 0
DECLARE	@SpecifiedDays	INT = 0
DECLARE @clients		XML
DECLARE	@csReleaseId	INT
DECLARE	@lastTime		INT = NULL

CREATE TABLE #ClientGroupSummary (ClientGroupId INT, days INT, metSLA INT, missedSLA INT, excluded INT)
CREATE TABLE #AppTypeSummary (appTypeId INT, metSLA INT, missedSLA INT)

SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

IF  @csReleaseId > 15 AND @isPrivateRunning = 'Metrics Reporting'
	AND EXISTS (SELECT * FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'Metrics Reporting - SLA for multiple days' AND CAST(value AS NVARCHAR(32)) = '1')
	SET @MultiDaySLA = 1


-- Get not-protected clients/subclients and the reason for not being protected
IF	@csReleaseId < 15
BEGIN
	DECLARE	@Today		DATETIME = DATEDIFF(DAY, 0, GETDATE()) 
	DECLARE	@StartTime	INT

	SET	@StartTime = dbo.GetUnixTime(DATEADD(DAY, -@LastNDays, @Today))

	CREATE TABLE #tblSubclient (ClientId INT, AppTypeId INT, InstanceId INT, BackupsetId INT, AppId INT, 
			BackupScheduled INT, Deconfigured INT, Disabled INT, Status NVARCHAR(64), Category NVARCHAR(64))

	IF	@specialGroupExists = 0
		INSERT	INTO #tblSubclient
		SELECT	A.clientId, A.appTypeId, A.instance, A.backupSet, A.id, 
				0, CASE WHEN (A.subclientStatus & 6) = 0 THEN 0 ELSE 1 END AS Deconfigured, 0, '', ''
		FROM	APP_Application A 
				INNER JOIN APP_Client CL ON CL.id = A.clientId
				INNER JOIN APP_InstanceName INST ON INST.id = A.instance
		WHERE	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
			AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
			AND A.subclientStatus&(16|32) = 0 
			AND CL.origCCId NOT IN (SELECT id FROM APP_CommCell WHERE type > 1) AND (INST.status & 2048) = 0
			AND A.id NOT IN (SELECT componentNameId FROM APP_subclientProp WHERE attrName = 'DDB Backup' AND attrVal <> '0' AND modified = 0)
			AND A.clientId NOT IN (SELECT componentNameId FROM APP_ClientProp WHERE attrName = 'Content Index Server' AND attrVal = '1')
	ELSE
		INSERT	INTO #tblSubclient
		SELECT	A.clientId, A.appTypeId, A.instance, A.backupSet, A.id, 
				0, CASE WHEN (A.subclientStatus & 6) = 0 THEN 0 ELSE 1 END AS Deconfigured, 0, '', ''
		FROM	APP_Application A 
				INNER JOIN APP_Client CL ON CL.id = A.clientId
				INNER JOIN APP_InstanceName INST ON INST.id = A.instance
				INNER JOIN (SELECT DISTINCT clientId FROM APP_ClientGroupAssoc WHERE clientGroupId IN (SELECT groupId FROM @includeGroups)
				) CGA ON CGA.clientId = A.clientId
		WHERE	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
			AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
			AND A.subclientStatus&(16|32) = 0 
			AND CL.origCCId NOT IN (SELECT id FROM APP_CommCell WHERE type > 1) AND (INST.status & 2048) = 0
			AND A.id NOT IN (SELECT componentNameId FROM APP_subclientProp WHERE attrName = 'DDB Backup' AND attrVal <> '0' AND modified = 0)
			AND A.clientId NOT IN (SELECT componentNameId FROM APP_ClientProp WHERE attrName = 'Content Index Server' AND attrVal = '1')

	UPDATE	#tblSubclient
	SET		Disabled = 1
	FROM	#tblSubclient S INNER JOIN JMJobAction J 
		ON	J.opType = 4 AND J.action = 1 AND (J.clientId = S.ClientId AND J.appType IN (S.AppTypeId, 0) OR J.appId = S.AppId)

	CREATE TABLE #tblSubclientAggr (AppId INT, ClientId INT, nSuccessfulJobs INT, nUnsuccessfulJobs INT, 
			LastJobID INT, LastJobStart INT, LastJobStatus INT)

	INSERT	INTO #tblSubclientAggr
	SELECT	S.AppId,
			SUM(CASE WHEN B.jobId IS NOT NULL AND B.status IN (1, 3, 14) THEN 1 ELSE 0 END),
			SUM(CASE WHEN B.jobId IS NOT NULL AND B.status NOT IN (1, 3, 14) THEN 1 ELSE 0 END),
			MAX(CASE WHEN B.status IN (1, 3, 14) THEN B.jobId ELSE 0 END), 
			MAX(B.jobId), 0, 0
	FROM	#tblSubclient S INNER JOIN JMBkpStats B ON B.appId = S.AppId
	WHERE	B.servEndDate > @StartTime AND B.bkpLevel IN (1, 2, 4, 256, 1024, 32768) AND B.commCellId = 2
	GROUP BY S.AppId

	UPDATE	#tblSubclientAggr
	SET		LastJobStart = B.servStartDate, 
			LastJobStatus = B.status
	FROM	#tblSubclientAggr S INNER JOIN JMBkpStats B
		ON B.jobId = S.LastJobId AND B.commCellId = 2

	CREATE TABLE #tblBackupTask (ClientGroupId INT, ClientId INT, AppTypeId INT, InstanceId INT, BackupsetId INT, AppId INT)

	INSERT	INTO #tblBackupTask
	SELECT	DISTINCT AE.clientGroupId, AE.clientId, AE.apptypeId, AE.instanceId, AE.backupsetId, AE.subclientId
	FROM	TM_AssocEntity AE 
	        INNER JOIN TM_SubTask ST ON AE.taskId = ST.taskId
	        INNER JOIN TM_SubTaskOptions STO ON STO.subTaskId = ST.SubTaskId
	        INNER JOIN TM_Task T ON T.taskId = ST.taskId
	WHERE	STO.optionId = 458405394 AND STO.value IN ('1', '2', '3', '6')
		AND ST.subTaskType = 2
		AND T.disabled = 0
		AND (AE.clientGroupId = 0 AND AE.taskId NOT IN (SELECT DISTINCT taskId FROM TM_AssocFilter WHERE filter_type = 2 AND filter_value = 124)
		  OR AE.clientGroupId > 0 AND AE.taskId NOT IN (SELECT taskId FROM TM_Task WHERE taskName = 'System Created for Content Store' AND taskType = 4))

	UPDATE	#tblSubclient
	SET		BackupScheduled = 1
	FROM	#tblSubclient S, #tblBackupTask T
	WHERE (	T.ClientId = 0 AND T.ClientGroupId = 0
		OR	T.ClientId > 0 AND S.ClientId = T.ClientId 
			AND S.AppTypeId IN (T.AppTypeId, 0) AND S.InstanceId IN (T.InstanceId, 0) AND S.BackupsetId IN (T.BackupsetId, 0) AND S.AppId IN (T.AppId, 0) )

	UPDATE	#tblSubclient
	SET		BackupScheduled = 1
	FROM	#tblSubclient S, APP_ClientGroupAssoc CGA, 
			(SELECT ClientGroupId FROM #tblBackupTask WHERE ClientId = 0 AND ClientGroupId > 0) T
	WHERE	S.BackupScheduled = 0 AND S.ClientId = CGA.clientId AND CGA.ClientGroupId = T.ClientGroupId

	DROP TABLE #tblBackupTask

	UPDATE	#tblSubclient
	SET		Status = CASE 
				WHEN S.Deconfigured > 0 OR S.Disabled > 0 OR CL.clientDescription LIKE '%offline%' THEN 'Excluded'
				WHEN ISNULL(A.nSuccessfulJobs, 0) > 0 THEN 'Protected'
				ELSE 'Not Protected' END,
			Category = CASE 
				WHEN S.Deconfigured > 0			THEN 'Deconfigured'
				WHEN S.Disabled > 0				THEN 'Backup Disabled'
				WHEN CL.clientDescription LIKE '%offline%'	THEN 'Offline' 
				WHEN ISNULL(A.nSuccessfulJobs, 0) > 0		THEN 'Protected'
				WHEN ISNULL(A.nUnsuccessfulJobs, 0) = 0 AND S.BackupScheduled = 1 
												THEN 'Backup May Be Scheduled At A Later Time'
				WHEN ISNULL(A.nUnsuccessfulJobs, 0) = 0 AND S.BackupScheduled = 0 
												THEN 'Backup Not Scheduled'
				ELSE 'Not Protected' END
	FROM	#tblSubclient S
			INNER JOIN APP_Client CL ON CL.id = S.ClientId
			LEFT OUTER JOIN #tblSubclientAggr A ON A.AppId = S.AppId
			

	CREATE TABLE #TblNotProtectedClient (ClientId INT)
	CREATE TABLE #TblProtectedClient (ClientId INT)

	INSERT INTO #TblNotProtectedClient
	SELECT DISTINCT ClientId FROM #TblSubclient 
	WHERE Category = 'Not Protected'
	
	SET	@nNotProtected = @@ROWCOUNT
	
	INSERT INTO #TblProtectedClient
	SELECT DISTINCT S.ClientId FROM #TblSubclient S LEFT OUTER JOIN #TblNotProtectedClient N ON S.ClientId = N.ClientId
	WHERE S.Category = 'Protected' AND N.ClientId IS NULL
	
	SET	@nProtected = @@ROWCOUNT

	SELECT @nExcluded = COUNT(DISTINCT S.ClientId) FROM #TblSubclient S 
		LEFT OUTER JOIN #TblNotProtectedClient N ON S.ClientId = N.ClientId
		LEFT OUTER JOIN #TblProtectedClient P ON S.ClientId = P.ClientId
	WHERE S.Category = 'Excluded' AND N.ClientId IS NULL AND P.ClientId IS NULL
	
	SET @surveyXML = (
	SELECT
		@Today as '@SLAEndDate',
		(SELECT 
			@LastNDays as '@days',
			@nProtected as '@nProtectedClients',
			@nNotProtected as '@nNotProtectedClients',
			@nExcluded as '@nExcludedClients'
		FOR XML PATH ('summary'), TYPE
		),
		(SELECT 
			@LastNDays as '@d1',
			S.ClientId as '@d2',
			S.AppId as '@d3',
			S.Category as '@d4',
			ISNULL(LastJobID, 0) as '@d5',
			ISNULL(LastJobStatus, 0) as '@d6',
			ISNULL(dbo.GetDateTime(LastJobStart), '1970-01-01') as '@d7',
			dbo.GetClientOwners(S.ClientId) AS '@d8'
		FROM	#TblSubclient S
			INNER JOIN #TblNotProtectedClient N ON S.ClientId = N.ClientId
			LEFT OUTER JOIN #tblSubclientAggr A ON A.AppId = S.AppId
		WHERE S.Category = 'Not Protected'
		FOR XML PATH ('d'), TYPE
		)
	FOR XML PATH ('NotProtectedClients') 
	-- Replace <<RESULTS_NAME>> with specific name
	)

	DROP TABLE #TblNotProtectedClient
	DROP TABLE #TblProtectedClient
	DROP TABLE #tblSubclient
	DROP TABLE #tblSubclientAggr
END
ELSE
IF  OBJECT_ID('RptGetSLA', 'P') IS NOT NULL AND @MultiDaySLA = 0 AND EXISTS (SELECT * FROM RptSLAClient WHERE days = -1)
BEGIN
	CREATE TABLE #SLA_Summary (clientType VARCHAR(32), metSLA INT, missedSLA INT, excluded INT)
	CREATE TABLE #SLA_Clients (clientId INT, clientType INT, status INT, category INT, slaDays INT)

	DECLARE @DateAsOf DATETIME
	
	EXEC RptGetSLA 1, -1, 0, 0, @DateAsOf OUTPUT

	IF @specialGroupExists = 1
	BEGIN
		DELETE	S
		FROM	#SLA_Clients S
				LEFT OUTER JOIN (
					SELECT	DISTINCT CGA.clientId	
					FROM	APP_ClientGroupAssoc CGA WITH (NOLOCK) 
						INNER JOIN @includeGroups G ON CGA.clientGroupId = G.groupID
					) T ON S.clientId = T.clientId
		WHERE	T.clientId IS NULL
		
		DELETE	D
		FROM	#SLA_Clients D
				LEFT OUTER JOIN #SLA_Clients C ON D.clientId = C.clientId
		WHERE	C.clientId IS NULL

		DELETE	FROM #SLA_Summary

		INSERT	INTO #SLA_Summary
		SELECT	'All', SUM(CASE status WHEN 1 THEN 1 ELSE 0 END), SUM(CASE status WHEN 2 THEN 1 ELSE 0 END), SUM(CASE status WHEN 3 THEN 1 ELSE 0 END)
		FROM	#SLA_Clients
	END

	INSERT INTO #ClientGroupSummary
	SELECT	clientGroupId, -1, ISNULL([1], 0), ISNULL([2], 0), ISNULL([3], 0)
	FROM	(
			SELECT	CGA.clientGroupId, 
					S.status, COUNT(S.clientId) AS nClients
			FROM	#SLA_Clients S WITH (NOLOCK) 
					INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId 
			GROUP BY CGA.clientGroupId, S.status) T
			PIVOT ( MAX(nClients) FOR status IN ([1], [2], [3])	) P

	UPDATE	T SET days = S.slaDays
	FROM	#ClientGroupSummary T
			INNER JOIN (
				SELECT	CGA.clientGroupId, 
						CASE WHEN MAX(S.slaDays) = MIN(S.slaDays) THEN MAX(S.slaDays) ELSE -1 END AS slaDays
				FROM	#SLA_Clients S WITH (NOLOCK) 
						INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId 
				GROUP BY CGA.clientGroupId			
			) S ON T.clientGroupId = S.clientGroupId
	
	INSERT INTO #AppTypeSummary	
	SELECT	T.appTypeId, SUM(T.metSLA) AS metSLA, COUNT(*)-SUM(T.metSLA) AS missedSLA
	FROM	(
		SELECT	S.clientId, S.appTypeId, MAX(CASE WHEN S.category IN (1, 13) THEN 1 ELSE 0 END) AS metSLA
		FROM	RptSLASubclient S WITH(NOLOCK)
				INNER JOIN RptSLAClient C WITH(NOLOCK) ON S.clientId = C.clientId 
					AND C.days = -1 AND C.fullJobsOnly = 0 AND C.status IN (1,2)
		WHERE	S.fullJobsOnly = 0 AND S.category IN (1, 2, 3, 4, 9, 12, 13)
		GROUP BY S.clientId, S.appTypeId
		) T 
	GROUP BY T.appTypeId
	
	SET @surveyXML = (
		SELECT
			@DateAsOf AS '@SLAEndDate',
			(SELECT 
				-1 AS '@days',
				metSLA    AS '@nProtectedClients',
				missedSLA AS '@nNotProtectedClients',
				excluded  AS '@nExcludedClients'
			FROM 	#SLA_Summary
			WHERE	clientType = 'All'
			FOR XML PATH ('summary'), TYPE
			),
			(SELECT 
				appTypeId AS '@appTypeId',
				metSLA    AS '@nProtectedClients',
				missedSLA AS '@nNotProtectedClients'
			FROM 	#AppTypeSummary
			FOR XML PATH ('appType'), TYPE
			),
			(SELECT 
				ClientGroupId AS '@id',
				-days     AS '@d',
				metSLA    AS '@p',
				missedSLA AS '@n',
				excluded  AS '@e'
			FROM 	#ClientGroupSummary
			FOR XML PATH ('cg'), TYPE
			)
		FOR XML PATH ('NotProtectedClients') 
		-- Replace <<RESULTS_NAME>> with specific name
	)

	DROP TABLE #SLA_Summary
	DROP TABLE #SLA_Clients
END
ELSE
BEGIN
	CREATE TABLE #tempSLA
	(
		date			datetime,
		clientId		int,
		appId			int,
		fullJobsOnly	int,
		appTypeId		int,
		status			int,
		category		int,
		lastSeenTime	int
	)

	CREATE TABLE #tempSLAClient
	(
		date			datetime,
		days			int,
		fullJobsOnly	int,
		clientId		int,
		isProtected		int,
		category		int
	)

	DECLARE @Summary TABLE (days INT, status INT, counts INT)

	DECLARE	@ClientGroupSLADays TABLE (clientGroupId INT, days INT)

	CREATE TABLE #ClientSLADays (clientId INT, clientGroupId INT, days INT)

	CREATE TABLE #FailedSubClient (days INT, clientId INT, isVM INT, appTypeId INT, appId INT, category INT, 
			LastSuccTime INT, lastJobID INT, lastJobCommCellID INT, lastJobStartTime INT, lastJobStatus INT, lastJobOptype INT)

	IF @MultiDaySLA = 0
	BEGIN
		SELECT	@SpecifiedDays = longVal 
		FROM	APP_ComponentProp WITH (NOLOCK)
		WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3300 AND modified = 0

		IF @SpecifiedDays > 0 SET @LastNDays = @SpecifiedDays

		INSERT	INTO @ClientGroupSLADays
		SELECT	componentId, longVal 
		FROM	APP_ComponentProp WITH (NOLOCK)
		WHERE	componentType = 8 AND propertyTypeId = 3300 AND modified = 0

		IF EXISTS (SELECT * FROM @ClientGroupSLADays) SET @SpecifiedDays = -1
	END

	-- Only for EDC cells or exclude all EDC cells. No mixture. One capture time for all EDC cells.
	DECLARE	@IgnoreEDCCommCells	INT = 0
	DECLARE	@EDCDumpCaptureTime	INT = 0
	DECLARE	@IsEDC				INT = 0
	
	SELECT	@IgnoreEDCCommCells = CAST(CAST(value AS NVARCHAR(32)) AS INT)
	FROM	GXGlobalParam WITH (NOLOCK)
	WHERE	name = 'IgnoreEDCCellsForSLA'
	
	IF	@IgnoreEDCCommCells = 0
	BEGIN
		SELECT	@EDCDumpCaptureTime = CAST(CAST(value AS NVARCHAR(32)) AS INT)
		FROM	GXGlobalParam WITH (NOLOCK)
		WHERE	name = 'EDCDumpCaptureTime'
	
		IF	@EDCDumpCaptureTime > 0
			SET @IsEDC = 1
	END

	DECLARE	@currDate	DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
	DECLARE	@lastDate	DATETIME
	DECLARE	@endDate	DATETIME
	DECLARE	@fromDate	DATETIME
	
	IF @IsEDC = 1
	BEGIN
	    SET @lastDate = (
	        SELECT	MAX(date) 
	        FROM	RptSLA S WITH (NOLOCK)
	                INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
	                INNER JOIN APP_CommCell CC WITH (NOLOCK) ON A.origCCId = CC.id AND CC.type >= 100)

		INSERT INTO #tempSLA
		SELECT	date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime
		FROM	RptSLA WITH (NOLOCK)
		WHERE	date = @lastDate AND fullJobsOnly = 0 AND status IN (1, 2)

		INSERT INTO #tempSLAClient
		SELECT	date, days, fullJobsOnly, clientId, (status & 1), category
		FROM	RptSLAClient WITH (NOLOCK)
		WHERE	date = @lastDate AND days = @LastNDays AND fullJobsOnly = 0 AND status IN (1, 2)
	END
	ELSE
	BEGIN
		SET @lastDate = (SELECT MAX(date) FROM RptSLAClient WITH (NOLOCK))

		IF @lastDate IS NOT NULL
		BEGIN
			SET @currDate = DATEADD(HOUR, DATEPART(HOUR, @lastDate), @currDate)
			IF  @currDate > GETDATE()
				SET @currDate = DATEADD(DAY, -1, @currDate)

			SET @lastDate = @currDate

			IF OBJECT_ID('RptSLASubclient', 'U') IS NOT NULL
			BEGIN
				INSERT	INTO #tempSLAClient (date, days, fullJobsOnly, clientId, isProtected, category)
				SELECT	date, days, fullJobsOnly, clientId, CASE status WHEN 1 THEN 1 WHEN 3 THEN 3 ELSE 0 END, category
				FROM	RptSLAClient WITH (NOLOCK)
				WHERE	fullJobsOnly = 0 AND (days = @LastNDays OR @MultiDaySLA = 1 OR @SpecifiedDays = -1 OR days = 0 AND status = 3)
			
				INSERT	INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime)
				SELECT	S.date, S.clientId, S.appId, S.fullJobsOnly, S.appTypeId, S.status, S.category, S.lastSeenTime
				FROM	RptSLASubclient S WITH (NOLOCK)
						INNER JOIN RptSLAClient C WITH (NOLOCK) ON S.clientId = C.clientId
				WHERE	S.fullJobsOnly = 0 AND C.fullJobsOnly = 0 AND (C.days = @LastNDays OR @MultiDaySLA = 1 OR @SpecifiedDays = -1)
					AND C.status = 2 AND S.category IN (2,3,4,12)
	
				SELECT	@lastTime = CAST(CAST(value AS NVARCHAR(32)) AS INT) FROM GxGlobalParam WITH (NOLOCK) WHERE name = 'SLA updated time'
				SET	@endDate  = dbo.GetDateTime(@lastTime)
			END
			ELSE
			IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'RptOneDaySLA' AND PARAMETER_NAME = '@i_days')
			   AND @MultiDaySLA = 0 AND @SpecifiedDays <> -1
				EXEC RptOneDaySLA @lastDate, @LastNDays
			ELSE
				EXEC RptOneDaySLA @lastDate
		END
		ELSE
			SET @lastDate = @currDate

		DELETE #tempSLAClient WHERE fullJobsOnly <> 0
		DELETE #tempSLA WHERE fullJobsOnly <> 0 OR category >= 5

		IF EXISTS (SELECT * FROM @ClientGroupSLADays)
		BEGIN
			INSERT INTO #ClientSLADays
			SELECT	T.clientId, T.clientGroupId, T.days
			FROM	(SELECT CG.days, CGA.clientId, CGA.clientGroupId, ROW_NUMBER() OVER (PARTITION BY CGA.clientId ORDER BY CG.days) AS rowId
					FROM	@ClientGroupSLADays CG INNER JOIN APP_ClientGroupAssoc CGA ON CG.clientGroupId = CGA.clientGroupId) T
			WHERE	T.rowId = 1
		
			DELETE	T 
			FROM	#tempSLAClient T INNER JOIN #ClientSLADays D ON T.clientId = D.clientId
			WHERE	T.days > 0 AND T.days <> D.days
			
			DELETE	T 
			FROM	#tempSLAClient T LEFT OUTER JOIN #ClientSLADays D ON T.clientId = D.clientId
			WHERE	D.clientId IS NULL AND T.days > 0 AND T.days <> @LastNDays
		END
		ELSE
		IF	@MultiDaySLA = 0
			DELETE #tempSLAClient WHERE days > 0 AND days <> @LastNDays
	END
	
	IF	@specialGroupExists = 1
	BEGIN
		DELETE	#tempSLAClient
		FROM	#tempSLAClient S
				LEFT OUTER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
					ON S.clientId = CGA.clientId AND CGA.clientGroupId IN (SELECT groupID FROM @includeGroups)
		WHERE	CGA.clientId IS NULL

		DELETE	#tempSLA
		FROM	#tempSLA S LEFT OUTER JOIN #tempSLAClient C ON S.clientId = C.clientId
		WHERE	C.clientId IS NULL
	END

	IF	@lastTime IS NULL
	BEGIN
		SET	@endDate	= DATEADD(DAY, 1, @lastDate)
		SET	@fromDate	= DATEADD(DAY, -30, @lastDate)
		SET	@lastTime	= dbo.GetUnixTime(dbo.LocalToUTCTime(@endDate, @csTimeZone))
	END

	DELETE	T
	FROM	#tempSLAClient T
			INNER JOIN APP_Client CL WITH (NOLOCK) ON T.clientId = CL.id
	WHERE	T.isProtected <> 3
		AND ((CL.specialClientFlags & 34) = 34 --Pseudo CommServe Client
		 OR (CL.status & 268435456) > 0 --Edge Drive Pseudo Client
		 OR CL.id IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1'))

	INSERT INTO @Summary
	SELECT	S.days, CASE WHEN S.category = 1 THEN 1 WHEN S.category IN (2,3,4,12) THEN 2 ELSE 3 END, COUNT(S.clientId)
	FROM	#tempSLAClient S WITH (NOLOCK) 
			INNER JOIN APP_Client C WITH (NOLOCK) ON S.clientId = C.id 
	WHERE	S.date = @lastDate AND S.fullJobsOnly = 0
		AND (S.days = @LastNDays OR S.days = 0 AND S.isProtected = 3 OR @MultiDaySLA = 1 OR @SpecifiedDays = -1)
	GROUP BY S.days, CASE WHEN S.category = 1 THEN 1 WHEN S.category IN (2,3,4,12) THEN 2 ELSE 3 END

	INSERT INTO #ClientGroupSummary
	SELECT	clientGroupId, -1, ISNULL([1], 0), ISNULL([2], 0), ISNULL([3], 0)
	FROM	(SELECT	CGA.clientGroupId, 
					CASE WHEN S.category = 1 THEN 1 WHEN S.category IN (2,3,4,12) THEN 2 ELSE 3 END AS status, 
					COUNT(S.clientId) AS nClients
			FROM	#tempSLAClient S WITH (NOLOCK) 
					INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId 
			WHERE	S.date = @lastDate AND S.fullJobsOnly = 0
				AND (S.days = @LastNDays OR S.days = 0 AND S.isProtected = 3 OR @MultiDaySLA = 1 OR @SpecifiedDays = -1)
			GROUP BY CGA.clientGroupId, CASE WHEN S.category = 1 THEN 1 WHEN S.category IN (2,3,4,12) THEN 2 ELSE 3 END) T
			PIVOT ( MAX(nClients) FOR status IN ([1], [2], [3])	) P

	UPDATE	T SET days = S.slaDays
	FROM	#ClientGroupSummary T
			INNER JOIN (
				SELECT	CGA.clientGroupId, 
						CASE WHEN MAX(S.days) = MIN(S.days) THEN MAX(S.days) ELSE -1 END AS slaDays
				FROM	#tempSLAClient S WITH (NOLOCK) 
						INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId 
				GROUP BY CGA.clientGroupId			
			) S ON T.clientGroupId = S.clientGroupId

	INSERT INTO #tempSLA
	SELECT	S.*
	FROM	RptSLA S WITH (NOLOCK)
			INNER JOIN #tempSLA T ON S.clientId = T.clientId
				AND (CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END) = (CASE WHEN T.appTypeId = 106 THEN 0 ELSE T.appId END)
			INNER JOIN #tempSLAClient C ON T.clientId = C.clientId
	WHERE	S.date >= DATEADD(DAY, -C.days, @lastDate) AND S.date <= @lastDate
		AND S.fullJobsOnly = 0 AND S.category IN (1,2,3,4,12,13) AND T.category IN (1,2,3,4,12,13)

	-- #define CV_COMPONENT_COMMCELL_SNAP_JOBS_QUALIFY_SLA 3303
	DECLARE @SnapJobsQualifySLA INT = 0

	SELECT	@SnapJobsQualifySLA = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3303 AND modified = 0

	IF	@SnapJobsQualifySLA = 1
		UPDATE #tempSLA SET status = 1, category = 1 WHERE category = 12

	UPDATE #tempSLA SET status = CASE WHEN category IN (1, 13) THEN 1 WHEN category IN (2,3,4,12) THEN 2 ELSE 3 END


	-- #define CV_COMPONENT_COMMCELL_EXCLUDE_CMDLINE_FROM_SLA 3308
	DECLARE @ExcludeCmdlineSubclients INT = 0

	SELECT	@ExcludeCmdlineSubclients = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3308 AND modified = 0
	
	-- Exclude protected instances and backupsets of DB agents
	CREATE TABLE #tempDBSubclient (clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT, isCmdLineApp INT)
	CREATE TABLE #newSubclients (appId INT)

	-- #define CV_COMPONENT_COMMCELL_SLA_GRACE_PERIOD_DAYS 3309
	DECLARE @GracePeriodDays INT = -1
	DECLARE	@maxCreationTime INT = 0

	SELECT	@GracePeriodDays = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3309 AND modified = 0

	IF	@GracePeriodDays > 0
		SET @maxCreationTime = dbo.GetUnixTime(dbo.LocalToUTCTime(DATEADD(DAY, -@GracePeriodDays, @lastDate), @csTimeZone))


	DECLARE @tblRange TABLE (days INT)
	DECLARE	@fromTime  INT
	DECLARE	@rangeDays INT 

	IF @MultiDaySLA = 1
	INSERT	INTO @tblRange
	SELECT	1  UNION ALL
	SELECT	2 UNION ALL
	SELECT	3  UNION ALL
	SELECT	5  UNION ALL
	SELECT	7  UNION ALL 
	SELECT	14 UNION ALL	
	SELECT	30
	ELSE
	INSERT	INTO @tblRange
	SELECT	@LastNDays UNION 
	SELECT	DISTINCT days FROM @ClientGroupSLADays

	DECLARE CurRange CURSOR FOR
	SELECT	days
	FROM	@tblRange

    OPEN  CurRange
    FETCH CurRange INTO @rangeDays
    WHILE @@FETCH_STATUS = 0 
    BEGIN
	SET @fromDate = DATEADD(DAY, -@rangeDays, @lastDate)
	SET @fromTime = dbo.GetUnixTime(dbo.LocalToUTCTime(@fromDate, @csTimeZone))

	INSERT INTO #FailedSubClient
	SELECT	@rangeDays, A.clientId, ISNULL(CAST(CP.attrVal AS INT), 0), A.appTypeId, CASE WHEN A.appTypeId = 106 THEN 0 ELSE A.appId END AS appId, 
			CASE WHEN MIN(A.category) = 1 THEN 1 WHEN MIN(A.category%12) = 0 THEN 12 ELSE MIN(A.category%12) END AS Category, 0, 0, 0, 0, 0, 0
	FROM	#tempSLAClient S WITH (NOLOCK) 
			INNER JOIN #tempSLA A WITH (NOLOCK) ON S.clientId = A.clientId AND S.category IN (2,3,4,12) AND S.days = @rangeDays AND A.date >= @fromDate
			LEFT OUTER JOIN APP_ClientProp AS CP WITH (NOLOCK) ON S.clientId = CP.componentNameId AND CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified = 0
	GROUP BY A.clientId, ISNULL(CAST(CP.attrVal AS INT), 0), A.appTypeId, CASE WHEN A.appTypeId = 106 THEN 0 ELSE A.appId END

	-- Ignore other agents if VMs are protected by VSA
	DELETE	T FROM #FailedSubClient T
			INNER JOIN (
				SELECT DISTINCT clientId FROM #FailedSubClient WHERE isVM = 1 AND appTypeId = 106 AND category = 1
			) S ON T.clientId = S.clientId
	WHERE	T.isVM = 1 AND T.appTypeId <> 106

	-- Ignore VSA if VMs are protected by other agents
	DELETE	T FROM #FailedSubClient T
			INNER JOIN (
				SELECT clientId, COUNT(DISTINCT appId) AS nSub FROM #FailedSubClient WHERE isVM = 1 AND appTypeId <> 106 AND category = 1 GROUP BY clientId
			) S ON T.clientId = S.clientId
			INNER JOIN (
				SELECT clientId, COUNT(DISTINCT appId) AS nSub FROM #FailedSubClient WHERE isVM = 1 AND appTypeId <> 106 AND category IN (1, 2, 3, 4, 12) GROUP BY clientId
			) A ON T.clientId = A.clientId
	WHERE	T.isVM = 1 AND T.appTypeId = 106 AND S.nSub = A.nSub AND S.nSub > 0

	INSERT	INTO #tempDBSubclient
	SELECT	A.clientId, A.AppTypeId, A.instance, A.backupSet, A.id, (A.subclientStatus & (64|4096))
	FROM	APP_Application A WITH (NOLOCK)
			INNER JOIN APP_BackupSetName B WITH (NOLOCK) ON A.backupSet = B.id	
			INNER JOIN APP_InstanceName I WITH (NOLOCK) ON A.instance = I.id
	WHERE	A.AppTypeId IN (3,22,37,61,62,79,80,103,104,128,135) AND (A.subclientStatus & 16) = 0 AND (B.status & 16) = 0 AND (I.status & 16) = 0


	IF @ExcludeCmdlineSubclients > 0
	BEGIN
		DELETE	S FROM #FailedSubClient S
			INNER JOIN #tempDBSubclient T ON S.appId = T.appId AND T.isCmdLineApp > 0

		DELETE	#tempDBSubclient WHERE isCmdLineApp > 0
	END

-- For Informix, SyBase, Oracle, SAP for Oracle, Oracle for SAP DB and Oracle RAC
	-- Skip cmdline subclient that has no job when the gui subclients have jobs
	;WITH DBInstanceProtectedByGuiSubclients (clientId, appTypeId, instanceId) AS
	(
		SELECT	A.clientId, B.appTypeId, B.instanceId
		FROM	#FailedSubClient A
				INNER JOIN #tempDBSubclient B ON A.appId = B.appId AND isCmdLineApp = 0 AND Category IN (1, 2, 12)
		WHERE	A.days = @rangeDays AND B.AppTypeId IN (3,5,22,61,79,80,104,128)
	)
	DELETE	#FailedSubClient
	FROM	(SELECT	S.appId
			FROM	#tempDBSubclient S
					INNER JOIN DBInstanceProtectedByGuiSubclients G
				ON S.clientId = G.clientId AND S.appTypeId = G.appTypeId AND S.instanceId = G.instanceId
				AND S.isCmdLineApp > 0
			) T
	WHERE	#FailedSubClient.days = @rangeDays AND #FailedSubClient.appId = T.appId AND #FailedSubClient.Category NOT IN (1, 2, 12)

	-- Skip gui subclients that have no job when the command line subclient is protected
	;WITH DBInstanceProtectedByCmdLine (clientId, appTypeId, instanceId) AS
	(
		SELECT	A.clientId, B.appTypeId, B.instanceId
		FROM	#FailedSubClient A
				INNER JOIN #tempDBSubclient B ON A.appId = B.appId AND isCmdLineApp > 0 AND Category IN (1, 12)
		WHERE	A.days = @rangeDays AND B.AppTypeId IN (3,5,22,61,79,80,104,128)
	)
	DELETE	#FailedSubClient
	FROM	(SELECT	S.appId
			FROM	#tempDBSubclient S
					INNER JOIN DBInstanceProtectedByCmdLine C
				ON S.clientId = C.clientId AND S.appTypeId = C.appTypeId AND S.instanceId = C.instanceId
				AND S.isCmdLineApp = 0
			) T
	WHERE	#FailedSubClient.days = @rangeDays AND #FailedSubClient.appId = T.appId AND #FailedSubClient.Category <> 2

-- For DB2, UNIX DB2, DB2 DPF and SAP HANA
	-- Skip cmdline subclient that has no job when the gui subclients have jobs
	;WITH DB2BackupsetProtectedByGuiSubclients (clientId, appTypeId, instanceId, backupsetId) AS
	(
		SELECT	A.clientId, B.appTypeId, B.instanceId, B.backupsetId
		FROM	#FailedSubClient A
				INNER JOIN #tempDBSubclient B ON A.appId = B.appId AND Category IN (1, 2, 12)
		WHERE	A.days = @rangeDays AND B.AppTypeId IN (37,62,103,135)
			AND B.isCmdLineApp = 0
	)
	DELETE	#FailedSubClient
	FROM	(SELECT	S.appId
			FROM	#tempDBSubclient S
					INNER JOIN DB2BackupsetProtectedByGuiSubclients G
				ON S.clientId = G.clientId AND S.appTypeId = G.appTypeId AND S.instanceId = G.instanceId AND S.backupsetId = G.backupsetId
				 AND S.isCmdLineApp > 0
			) T
	WHERE	#FailedSubClient.days = @rangeDays AND #FailedSubClient.appId = T.appId AND #FailedSubClient.Category NOT IN (1, 2, 12)

	-- Skip gui subclients that have no job when the command line subclient is protected
	;WITH DB2BackupsetProtectedByCmdLine (clientId, appTypeId, instanceId, backupsetId) AS
	(
		SELECT	A.clientId, B.appTypeId, B.instanceId, B.backupsetId
		FROM	#FailedSubClient A
				INNER JOIN #tempDBSubclient B ON A.appId = B.appId AND Category IN (1, 12)
		WHERE	A.days = @rangeDays AND B.AppTypeId IN (37,62,103,135)
			 AND (isCmdLineApp = 64 OR isCmdLineApp = 4096 AND B.appTypeId <> 135)
	)
	DELETE	#FailedSubClient
	FROM	(SELECT	S.appId
			FROM	#tempDBSubclient S
					INNER JOIN DB2BackupsetProtectedByCmdLine C
				ON S.clientId = C.clientId AND S.appTypeId = C.appTypeId AND S.instanceId = C.instanceId AND S.backupsetId = C.backupsetId
				AND (S.isCmdLineApp = 0 OR S.isCmdLineApp = 4096 AND S.appTypeId = 135)
			) T
	WHERE	#FailedSubClient.days = @rangeDays AND #FailedSubClient.appId = T.appId AND #FailedSubClient.Category <> 2


	TRUNCATE TABLE #tempDBSubclient
	TRUNCATE TABLE #newSubClients

	DELETE	#FailedSubClient WHERE days = @rangeDays AND category = 1


	IF	@GracePeriodDays = -1 --use SLA days
		SET @maxCreationTime = @fromTime

	-- Exclude recently created subclients that have no job
	INSERT	INTO #newSubclients
	SELECT	componentNameId
	FROM	APP_SubClientProp SP WITH (NOLOCK)
		INNER JOIN #FailedSubClient S ON SP.componentNameId = S.appId AND S.appTypeId <> 106 AND S.category IN (3, 4)
	WHERE	created > 0
	GROUP BY componentNameId
	HAVING MIN(created) > @maxCreationTime

	DELETE	S FROM #FailedSubClient S INNER JOIN #newSubclients NS ON S.appId = NS.appId AND S.appTypeId <> 106 AND S.days = @rangeDays


	UPDATE	#FailedSubClient
	SET		LastSuccTime = T.LastSuccTime, LastJobID = T.LastJobID, lastJobCommCellID = T.CommCellID
	FROM	#FailedSubClient A INNER JOIN
			(SELECT	S.appId, J.commCellId, MAX(J.jobId) AS LastJobID, 
					MAX(CASE WHEN J.status IN (1,14) OR J.status = 3 AND I.isCWEjobValid = 1 THEN J.servStartDate ELSE 0 END) AS LastSuccTime
			FROM	#FailedSubClient S 
				INNER JOIN JMBkpStats J WITH (NOLOCK)
						ON J.appId = S.appId AND S.appTypeId <> 106 AND J.servStartDate < @lastTime
						AND J.failureErrorCode <> 8
						AND J.bkpLevel IN (1, 2, 4, 256, 1024, 32768) 
						AND J.opType IN (4, 18, 30, 43, 59, 65, 76, 87, 91, 98, 101) 
						AND S.days = @rangeDays
				INNER JOIN APP_iDAType I WITH (NOLOCK) ON J.appType = I.type
			WHERE (@IsEDC = 0 AND J.commCellId = 2 OR @IsEDC = 1 AND J.commCellId > 2)
			GROUP BY S.appId, J.commCellId) T ON T.AppId = A.AppId
	WHERE	A.days = @rangeDays

	UPDATE	#FailedSubClient
	SET		LastSuccTime = CASE WHEN T.LastSuccTime > A.LastSuccTime THEN T.LastSuccTime ELSE A.LastSuccTime END, 
			LastJobID = T.LastJobID, lastJobCommCellID = T.CommCellID
	FROM	#FailedSubClient A INNER JOIN
			(SELECT	S.ClientId, J.commCellId, MAX(J.jobId) AS LastJobID, MAX(CASE WHEN V.status IN (0,3) THEN J.servStartDate ELSE 0 END) AS LastSuccTime
			FROM	#FailedSubClient S
					INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK)
						ON V.clientId = S.ClientId AND S.appTypeId = 106 
					INNER JOIN JMBkpStats J WITH (NOLOCK)
						ON J.jobId = V.jobId AND J.commCellId = V.commCellId 
						AND J.servStartDate < @lastTime AND J.failureErrorCode <> 8
 						AND J.bkpLevel IN (1, 2, 4, 256, 1024, 32768) 
						AND J.opType IN (4, 18, 30, 43, 59, 65, 76, 87, 91, 98, 101)
			WHERE (@IsEDC = 0 AND J.commCellId = 2 OR @IsEDC = 1 AND J.commCellId > 2)
			GROUP BY S.ClientId, J.commCellId) T ON T.ClientId = A.ClientId
	WHERE	A.days = @rangeDays AND A.appTypeId = 106

	UPDATE	#FailedSubClient
	SET		AppId = J.appId, lastJobStartTime = J.servStartDate, LastJobStatus = dbo.GetJobStatusForReport(J.status, 1), lastJobOptype = J.opType
	FROM	#FailedSubClient CJ
			INNER JOIN JMBkpStats J WITH (NOLOCK) ON J.jobId = CJ.LastJobID AND J.commCellId = CJ.lastJobCommCellID
	WHERE	CJ.days = @rangeDays

	UPDATE	#FailedSubClient
	SET		AppId = S.appId
	FROM	#tempSLA S
	WHERE	#FailedSubClient.days = @rangeDays AND #FailedSubClient.clientId = S.clientId AND #FailedSubClient.appTypeId = 106 AND #FailedSubClient.appId = 0 
		AND S.date = @lastDate AND S.appTypeId = 106

        FETCH	CurRange INTO @rangeDays
    END
    CLOSE CurRange
    DEALLOCATE CurRange

	DROP TABLE #newSubclients 
	DROP TABLE #tempDBSubclient
	DROP TABLE #tempSLAClient
	DROP TABLE #tempSLA


	-- Consider "Nothing to backup" check for File System subclients
	UPDATE	F SET lastSuccTime = CAST(SP.attrVal AS INT) 
	FROM	#FailedSubClient F
		INNER JOIN APP_SubclientProp SP WITH (NOLOCK) ON F.appId = SP.componentNameId AND SP.attrName = 'Last Data Protected Time' AND SP.modified = 0
	WHERE	F.appTypeId <> 106

	-- Forever running jobs with optype 116 (STATELESS_BACKUP)
	IF EXISTS (SELECT * FROM JMJobInfo WITH (NOLOCK) WHERE opType = 116)
	BEGIN
		UPDATE	T SET LastJobID = J.jobId, lastJobCommCellID = J.commCellId, 
			lastJobStartTime = CASE WHEN J.lastUpdateFromClient = 0 THEN J.jobStartTime ELSE J.lastUpdateFromClient END, 
			lastJobStatus = dbo.GetJobStatusForReport(J.state, 0), lastJobOptype = 116
		FROM	#FailedSubClient T
			INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON T.AppId = B.applicationId
			INNER JOIN JMJobInfo J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commcellId = J.commCellId AND J.opType = 116
		WHERE	T.lastJobStartTime = 0 OR J.lastUpdateFromClient > T.lastJobStartTime
	END

	IF EXISTS (SELECT clientId FROM #FailedSubClient WHERE appTypeId = 106)
		UPDATE	T SET LastJobID = JL.childJobId
		FROM	#FailedSubClient T
				INNER JOIN APP_Application A WITH (NOLOCK) ON T.clientId = A.clientId
				INNER JOIN JMJobDataLink JL WITH (NOLOCK) ON T.LastJobID = JL.parentJobId AND A.id = childAppid AND JL.linkType = 7
		WHERE	T.appTypeId = 106	

	SET @surveyXML = (
		SELECT
			@endDate AS '@SLAEndDate',
			(SELECT 
				(CASE WHEN @SpecifiedDays = 0 THEN days ELSE -1 END) AS '@days',
				SUM(CASE status WHEN 1 THEN counts ELSE 0 END) as '@nProtectedClients',
				SUM(CASE status WHEN 2 THEN counts ELSE 0 END) as '@nNotProtectedClients',
				SUM(CASE status WHEN 3 THEN counts ELSE 0 END) as '@nExcludedClients'
			FROM 	@Summary
			GROUP BY (CASE WHEN @SpecifiedDays = 0 THEN days ELSE -1 END)
			FOR XML PATH ('summary'), TYPE
			),
			(SELECT 
				ClientGroupId AS '@id',
				(CASE WHEN @SpecifiedDays = 0 THEN days ELSE -1 END) AS '@d',
				metSLA     AS '@p',
				missedSLA  AS '@n',
				excluded   AS '@e'
			FROM 	#ClientGroupSummary
			FOR XML PATH ('cg'), TYPE
			),
			(SELECT	
				(CASE WHEN @SpecifiedDays = 0 THEN S.days ELSE -S.days END) AS '@d1',
				S.ClientId AS '@d2',
				S.AppId AS '@d3',
				dbo.WR_GetSLACategoryString(0, S.category) AS '@d4',
				ISNULL(S.LastJobID, 0) AS '@d5',
				CASE WHEN ISNULL(S.LastJobStatus, 0) = 0 THEN 'N/A' 
				     WHEN @csReleaseId < 16 AND S.lastJobOptype IN (59, 65) AND S.LastJobStatus IN (1,2,3) THEN dbo.GetJobStatusString(0, S.LastJobStatus) + ' - No Backup Copy'
				     WHEN @csReleaseId >= 16 AND S.category = 12 AND S.LastJobStatus IN (1,2,3) THEN dbo.GetJobStatusString(0, S.LastJobStatus) + ' - No Backup Copy'
				     ELSE dbo.GetJobStatusString(0, S.LastJobStatus) END AS '@d6',
				ISNULL(dbo.GetDateTime(S.lastJobStartTime), '1970-01-01') AS '@d7',
				dbo.GetClientOwners(S.ClientId) AS '@d8',
				(CASE WHEN S.lastSuccTime > 0 THEN dbo.GetDateTime(S.lastSuccTime) ELSE '1970-01-01' END) AS '@d9'
			FROM	#FailedSubClient S
			ORDER BY S.days, S.ClientId, S.AppId
			FOR XML PATH ('d'), TYPE
		)
		FOR XML PATH ('NotProtectedClients') 
		-- Replace <<RESULTS_NAME>> with specific name
	)

	DROP TABLE #ClientSLADays
	DROP TABLE #FailedSubClient
END

DROP TABLE #ClientGroupSummary
DROP TABLE #AppTypeSummary

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
--SELECT cast (@surveyXML as XML)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

--------- END - GENERATED CODE ---------