--- Please follow the below comments to insert SQL statements.
-- Dash copy fallen behind
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

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

--------- END - GENERATED CODE ---------
DECLARE @STANDALONE INT = 0

/*  Standalone  
--   (UNCOMMENT OUT MULTI-LINE COMMENT DELIMITERS BEFORE DEPLOYMENT!):

SET @STANDALONE = 1
DECLARE @outputXML XML

*/


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

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

-- Query to get DDB Performance by Average Q&I Time 
-- Get Average Q&I time from Idxsidbusagehistory table

DECLARE @ONE_MB INT = (1024*1024)

declare @releaseId int

SET @releaseId =(SELECT releaseId from APP_Client  where id =2)

DECLARE @csSPVersion INT = 0
IF @releaseId = 16
    SELECT  @csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2	

DECLARE @ddbperformance XML
declare @nstring nvarchar(max)

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

IF ISNULL(@timzonename, '') = ''
BEGIN
    SELECT @timzonename = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @timzonename = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@timzonename, CHARINDEX(':', @timzonename, CHARINDEX(':', @timzonename, 0) + 1) + 1, 255)
END

----------DDB which needs to be upgraded to V5 DDB--------------
IF object_id('tempdb.dbo.#upgradeTov5DDBs') is not null DROP TABLE #upgradeTov5DDBs
create table #upgradeTov5DDBs( 
	SIDBStoreID INT
)

IF @releaseId >= 16 AND @csSPVersion >= 14
BEGIN
	DECLARE @str NVARCHAR(MAX);
	SET @str = N'INSERT INTO #upgradeTov5DDBs
	SELECT  S.SIDBStoreId
	FROM   IdxSIDBStore S WITH (READUNCOMMITTED)
	WHERE S.ExtendedFlags&4 <> 4 --IDX_SIDBSTORE_EX_FLAGS_MARK_AND_SWEEP_ENABLED
	        AND S.SIDBStoreId <> 0'
	exec(@str)
END
----------------------------------------------------------------

IF object_id('tempdb.dbo.#tmpStores') is not null DROP TABLE #tmpStores
create table #tmpStores( 
	StoreId int, StoreName nvarchar(256), MediaAgentNames nvarchar(1024), CreatedTime datetime, LastRecordedTime datetime, 
	AvgQITime int, PrimaryRecords bigint, SecondaryRecords bigint, ZeroRefRecords bigint, 
	DDBSizeMB bigint, TotalDataSizeMB bigint, InstancedDataSizeMB bigint,  
	FrontendBackupMB bigint, FrontendArchiveMB bigint, BaseLineSizeMB bigint default NULL, Readiness VARCHAR(128) DEFAULT 'N/A', ExtendedFlags BIGINT DEFAULT 0) -- MR 172535 Add BaseLineSizeMB

IF object_id('tempdb.dbo.#tmpSubStores') is not null DROP TABLE #tmpSubStores
CREATE TABLE #tmpSubStores(
	StoreId int, SubStoreId int, MediaAgentName nvarchar(1024), accessPath nvarchar(1024), 
	PrimaryRecords bigint, SecondaryRecords bigint, ZeroRefRecords bigint, AvgQITime int, LastRecordedTime datetime,
	partitionSize bigint
)
IF(@releaseId >= 15)    -- Release 10.0
BEGIN
	set @nstring =N'
	declare @tblLatestStoreInfo table (
		SIDBStoreId int, SubStoreId int, CommCellId int, latestUpdateTime int, QIValidTime int, avgQITime int, 
		primaryRecs bigint, secondaryRecs bigint, zeroref bigint)
    
    --Get most recent modified time for each Dedupe Store partition
    insert into @tblLatestStoreInfo
    select U.SIDBStoreId, U.SubStoreId, U.CommCellId, max(U.ModifiedTime), 0, 0, 0, 0, 0
    from IdxSIDBUsageHistory U WITH (NOLOCK)
		inner join archCopySIDBStore agcStore WITH (NOLOCK) ON U.SIDBStoreId = agcStore.SIDBStoreId
	where U.HistoryType <> 2 
    group by U.SIDBStoreId, U.SubStoreId, U.CommCellId

	if exists (	select	* from  sys.objects
				where	object_id = OBJECT_ID(N''GetCurrentAvgQITimeMedian'')
						and type in ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' ))
	begin
		update @tblLatestStoreInfo
		set avgQITime = dbo.GetCurrentAvgQITimeMedian(SIDBStoreId, SubStoreId , commcellId , 0)
    end
    
    else    
	begin
		--Get valied last modified time for QITime
		UPDATE T
		SET T.QIValidTime = T1.latestQIModifiedTime
		FROM @tblLatestStoreInfo T
			INNER JOIN (SELECT U.SIDBStoreId, U.SubStoreId, max(U.ModifiedTime) AS latestQIModifiedTime
						FROM IdxSIDBUsageHistory U WITH (NOLOCK)
							INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId AND U.SubStoreId = LI.SubStoreId
						WHERE U.AvgQITime > 0
						AND U.HistoryType <> 2
						GROUP BY U.SIDBStoreId, U.SubStoreId
						) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId AND T.SubStoreId = T1.SubStoreId
				
		--Take average of QITime for last 3 days.
		UPDATE T
		SET T.avgQITime = T1.QITime
		FROM @tblLatestStoreInfo T
			INNER JOIN (SELECT U.SIDBStoreId, U.SubStoreId, AVG(U.AvgQITime) AS QITime
						FROM IdxSIDBUsageHistory U WITH (NOLOCK)
							INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId AND U.SubStoreId = LI.SubStoreId
						WHERE U.ModifiedTime > (LI.QIValidTime - (3*24*60*60))
						AND U.AvgQITime > 0
						AND U.AvgQITimeSampleCount >= 100 -- Discard QI Times if sample count less than 100
						AND U.HistoryType = 0
						GROUP BY U.SIDBStoreId, U.SubStoreId
						) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId AND T.SubStoreId = T1.SubStoreId
						
	end
	     
	UPDATE T
	SET T.PrimaryRecs = U.PrimaryEntries,
		T.SecondaryRecs = U.SecondaryEntries,
		T.Zeroref = U.ZeroRefCount
	FROM @tblLatestStoreInfo T
		INNER JOIN IdxSIDBUsageHistory U WITH (NOLOCK) ON T.SIDBStoreId = U.SIDBStoreId AND T.SubStoreId = U.SubStoreId
	WHERE T.latestUpdateTime = U.ModifiedTime


	INSERT INTO #tmpStores (StoreId, LastRecordedTime, AvgQITime, primaryRecords, secondaryRecords, zeroRefRecords, BaseLineSizeMB)
	SELECT SIDBStoreId, dbo.getDateTime(MAX(latestUpdateTime)), MAX(AvgQITime), SUM(primaryRecs), SUM(secondaryRecs), SUM(zeroref), 0 --MR 172535 Add BaseLineSizeMB placeholder
	FROM @tblLatestStoreInfo
	GROUP BY SIDBStoreId
	INSERT INTO #tmpSubStores(StoreId, SubStoreId, accessPath, AvgQITime, PrimaryRecords, SecondaryRecords, ZeroRefRecords, LastRecordedTime)
	SELECT S.SIDBStoreId, S.SubStoreId, A.Path, AvgQITime, primaryRecs, secondaryRecs, zeroref, dbo.getDateTime(latestUpdateTime) 
	FROM IdxSIDBSubStore S
	INNER JOIN IdxAccessPath A ON A.IdxAccessPathId = S.IdxAccessPathId AND A.IdxCacheId = S.IdxCacheId
	INNER JOIN IdxCache C ON C.IdxCacheId = S.IdxCacheId
	INNER JOIN @tblLatestStoreInfo T ON T.SIDBStoreId = S.SIDBStoreId AND T.SubStoreId = S.SubStoreId
	
	UPDATE T
	SET DDBSizeMB = Size.DDBSizeMB
	FROM #tmpStores T
		inner join (SELECT SIDBStoreId, SUM(IC.DiskUsageMB) AS DDBSizeMB
						FROM  #tmpStores T1, IdxSIDBSubStore Sub WITH (NOLOCK), IdxCache IC WITH (NOLOCK)
						WHERE Sub.SIDBStoreId = T1.StoreId
						AND Sub.IdxCacheId = IC.IdxCacheId
						GROUP BY Sub.SIDBStoreId) AS Size ON T.StoreId = Size.SIDBStoreId
	UPDATE T
	SET T.partitionSize = IC.DiskUsageMB
	FROM #tmpSubStores T
	INNER JOIN IdxSIDBSubStore Sub
	ON Sub.SubStoreId = T.SubStoreId
	INNER JOIN IdxCache IC
	ON Sub.IdxCacheId = IC.IdxCacheId
						
UPDATE	S 
SET		Readiness = 
		CASE
			WHEN (I.flags & 1073741824 = 1073741824 OR SubStore.flags & 1073741824 = 1073741824) THEN ''Resync in progress''
			WHEN (I.flags & 33554432 = 33554432 OR SubStore.flags & 33554432 = 33554432)THEN ''Needs resync''
			WHEN (I.flags & 67108864 = 67108864 OR SubStore.flags & 67108864 = 67108864)THEN ''Verification in progress''
			WHEN (I.flags & 1024 = 1024 OR SubStore.flags & 1024 = 1024) THEN ''Partitions being moved''
			WHEN (I.flags & 16777216 = 16777216 OR SubStore.flags & 16777216 = 16777216) THEN ''Maintenance''
			ELSE ''Ready''
		END	
FROM	#tmpStores S
		INNER JOIN IdxSIDBStore I WITH (NOLOCK) ON S.StoreId = I.SIDBStoreId
		INNER JOIN IdxSIDBSubStore SubStore WITH (NOLOCK) ON I.SIDBStoreId = SubStore.SIDBStoreId
	
	; WITH StoreMA (StoreId, MAName) AS
	(
		SELECT	DISTINCT SIDBStoreId, C.name
		FROM	IdxSIDBSubStore S WITH (NOLOCK) INNER JOIN APP_Client C WITH (NOLOCK) ON S.ClientId = C.id
	)
	, StoreMAList (StoreId, MANames) AS
	(
		SELECT	StoreId,
				STUFF(
				  (SELECT '', '', '''' + MAName FROM StoreMA WHERE StoreId = S.StoreId ORDER BY MAName FOR XML PATH('''')
				  ), 1, 2, '''')
		FROM	StoreMA S
		GROUP BY StoreId
	)
	UPDATE T SET MediaAgentNames = L.MANames
	FROM #tmpStores T INNER JOIN StoreMAList L ON T.StoreId = L.StoreId
	
	UPDATE T SET MediaAgentName = C.name
	FROM APP_Client C 
	INNER JOIN IdxSIDBSubStore S
	ON C.Id = S.ClientId
	INNER JOIN #tmpSubStores T
	ON S.SubStoreId = T.SubStoreId
	'
END		
ELSE IF (@releaseId = 14)  --Release 9.0
BEGIN
	set @nstring =N'
	declare @tblLatestStoreInfo table (
	SIDBStoreId int, latestUpdateTime int, QIValidTime int, avgQITime int, 
	primaryRecs bigint, secondaryRecs bigint, zeroref bigint)

    --Get most recent modified time for each Dedupe Store
    insert into @tblLatestStoreInfo
    select U.SIDBStoreId, max(U.ModifiedTime), 0, 0, 0, 0, 0
    from IdxSIDBUsageHistory2 U WITH (NOLOCK)
		inner join archCopySIDBStore agcStore WITH (NOLOCK) ON U.SIDBStoreId = agcStore.SIDBStoreId
    group by U.SIDBStoreId
    
	--Get valied last modified time for QITime
	UPDATE T
	SET T.QIValidTime = T1.latestQIModifiedTime
	FROM @tblLatestStoreInfo T
		INNER JOIN (SELECT U.SIDBStoreId, max(U.ModifiedTime) AS latestQIModifiedTime
					FROM IdxSIDBUsageHistory2 U WITH (NOLOCK)
						INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId
					WHERE U.AvgQITime > 0
					GROUP BY U.SIDBStoreId
					) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId
    
	--Take average of QITime for last 3 days.
    UPDATE T
    SET T.avgQITime = T1.QITime
    FROM @tblLatestStoreInfo T
		INNER JOIN (SELECT U.SIDBStoreId, AVG(U.AvgQITime) AS QITime
					FROM IdxSIDBUsageHistory2 U WITH (NOLOCK)
						INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId
					WHERE U.ModifiedTime > (LI.QIValidTime - (3*24*60*60))
					AND U.AvgQITime > 0
					AND U.AvgQITimeSampleCount >= 100 -- Discard QI Times if sample count less than 100
					GROUP BY U.SIDBStoreId
					) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId
					
	UPDATE T
	SET T.PrimaryRecs = U.PrimaryEntries,
		T.SecondaryRecs = U.SecondaryEntries,
		T.Zeroref = U.zeroRefCount
	FROM @tblLatestStoreInfo T
		INNER JOIN IdxSIDBUsageHistory2 U ON T.SIDBStoreId = U.SIDBStoreId
	WHERE T.latestUpdateTime = U.ModifiedTime
					

	INSERT INTO #tmpStores (StoreId, LastRecordedTime, AvgQITime, primaryRecords, secondaryRecords, zeroRefRecords, BaseLineSizeMB)
	SELECT SIDBStoreId, dbo.getDateTime(MAX(latestUpdateTime)), MAX(AvgQITime), MAX(primaryRecs), SUM(secondaryRecs), MAX(zeroref), 0 --MR 172535 Add BaseLineSizeMB placeholder
	FROM @tblLatestStoreInfo
	GROUP BY SIDBStoreId


	UPDATE T
	SET StoreName = iss.SIDBStoreAliasName,
	MediaAgentNames = cl.name,
	createdTime = dbo.GetDateTime(iss.CreatedTime),
	DDBSizeMB = ic.DiskUsageMB
	FROM #tmpStores T
	inner join IdxSIDBStore iss WITH (NOLOCK) ON T.StoreId = iss.SIDBStoreId
	inner join IdxCache ic WITH (NOLOCK) ON iss.IdxCacheId = ic.IdxCacheId
	inner join APP_Client cl WITH (NOLOCK) ON iss.ClientId = cl.id'
END

exec sp_executesql @nstring


  
  /*  MR 172535:  FOR NOW, PUT EVERYTHING
   *  HERE.  LATER FIGURE OUT WHERE IT REALLY SHOuLD GO, AND
   * IF THERE ARE "Release Level" Dependancies
   */

--------------------------- START BaseLineSizeMB MODS -------------------------------------------------------
IF (@releaseId > 16) OR (@releaseId = 16 AND @csSPVersion >= 15)
BEGIN
	IF OBJECT_ID('tempdb..#lt_MMGetDDBEnginesOutput') IS NOT NULL DROP TABLE #lt_MMGetDDBEnginesOutput
	CREATE TABLE #lt_MMGetDDBEnginesOutput      
	(                                       
		CommCellId              int,       
		SIDBStoreId             int,        
		SIDBStoreName           nvarchar(256),        
	 	SIDBStoreAliasName      nvarchar(256),        
		archGroupId             integer,    
		archGroupName           nvarchar(256),      
		copyId                  int,        
		copyName                nvarchar(64), 
		totalDataSize           bigint,     
		sizeOnMedia             bigint,     
	    totalAppSize            BIGINT,    
	    deDupSavingSize         BIGINT,     
	    deletedAFCount          INT,        
	    MaintenanceReason       INT,        
	    jobCount                INT,        
	    baseLineSize            BIGINT,     
	    freeSpaceOnLibrary      BIGINT,     
	    retentionDaysForCopy    INT,        
	    retentionCyclesForCopy  INT,        
	    copyMediaAgentId        INT,        
	    sealedTime              INT,        
	    CreatedTime             INT,        
	    SubstoreCount           INT,        
	    AvgQITime               INT,        
	    UniqueBlocks            BIGINT,     
	    dedupeFlags             integer,    
	    PrimaryRecPercent       INT,        
	    Version                 INT,       
	    MaxAvgQITime            INT,        
	    Flags                   INT,        
	    LimitFlag               INT,        
	    AvgQITime14Days         INT,        
	    MaxAllowedConnections   INT,        
	    LastVerifiedOn          BIGINT,     
	    JobsLastVerifiedOn      BIGINT,     
	    AppBaselineSize         BIGINT,     
	    SizeOfFragmentedSpace   BIGINT,     
	    ActiveStreams           INT,       
	    EnableAfterDelayTimeZone INT,       
	    EnableAfterDelayTime    INT,        
	    SizeOffDiskMB           BIGINT,     
	    SizeToBeInSiloMB        BIGINT,     
	    SizeInSiloMB            BIGINT,    
	    SizeOfBackup            BIGINT,     
	    deDupSavingPercent      VARCHAR(20),
	    extendedFlags           BIGINT ,        
	    TotalSizeOfUniqueBlocks       BIGINT 
	)
	
	IF @releaseId = 16 AND @csSPVersion < 20
	BEGIN
		ALTER TABLE #lt_MMGetDDBEnginesOutput DROP COLUMN TotalSizeOfUniqueBlocks
	END
	
	EXEC MMGetDDBEngines 1 /*userId*/, 30 /*propertyLevel*/, 0, 0, 0 /*SIDBStoreId*/, 32
	
	UPDATE T
	SET BaseLineSizeMB = baseLineSize/1024.0/1024.0, FrontendBackupMB = AppBaselineSize/1024.0/1024.0, FrontendArchiveMB = 0
	FROM #TmpStores T 
	INNER JOIN #lt_MMGetDDBEnginesOutput L ON L.SIDBStoreId = T.StoreId
	IF OBJECT_ID('tempdb..#lt_MMGetDDBEnginesOutput') IS NOT NULL DROP TABLE #lt_MMGetDDBEnginesOutput
END
ELSE
BEGIN
-- THE FOLLOWING CODE "BORROWED" FROM MMGetDDBEngines.sp (REL_11_0_0_B80_SP12_BRANCH [1.17.12.13]) -Skatz 20180417

IF object_id('tempdb.dbo.#SIAppID') is not null DROP TABLE #SIAppID
 CREATE	TABLE #SIAppID(	SIDBStoreId INT, appId INT, archGrpId INT)


  --  Check diffs from tab <REL_9_0_0_SP14_BRANCH>
 -- JMJobDatastats		No changes that affect stmt
 -- archCopySIDBStore   FK changes only

IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL
BEGIN
	INSERT INTO #SIAppID
	SELECT DISTINCT B.StoreId, A.appId, A.archGrpId
	FROM JMJobDatastats A WITH (NOLOCK), archSubclientCopyDDBMap C WITH (NOLOCK), #TmpStores B
	WHERE A.archGrpCopyId = C.CopyID 
	AND	A.AppId = C.appId
	and C.SIDBStoreId = B.StoreId
	AND A.status IN (100, 101, 102, 103) 
	AND (A.disabled & (1 + 256)) = 0
END
ELSE
BEGIN
	INSERT INTO #SIAppID
	SELECT DISTINCT B.StoreId, A.appId, A.archGrpId
	FROM JMJobDatastats A WITH (NOLOCK), #TmpStores B, archCopySIDBStore C WITH (NOLOCK)
	WHERE A.archGrpCopyId = C.CopyID 
	and C.SIDBStoreId = B.StoreId
	AND A.status IN (100, 101, 102, 103) 
	AND (A.disabled & (1 + 256)) = 0
END

 --  Check diffs from tab <REL_9_0_0_SP14_BRANCH>
 --JMBkpStats - collumns added not reflected in folllowing stmt.
UPDATE	#TmpStores
 SET BaseLineSizeMB = 
	(SELECT	SUM(totalBackupSize)
		FROM	JMBkpStats BSL WITH (NOLOCK),
				(SELECT	BSL.appId, MAX(jobId) AS MaxFullJobID
					FROM	JMBkpStats BSL WITH (NOLOCK),	#SIAppID CT
				WHERE	CT.SIDBStoreId = #TmpStores.StoreId 
						AND CT.appId = BSL.appId
						AND BSL.status IN (1, 3, 14)
						AND BSL.bkpLevel IN (1, 0x40, 0x80, 0x400, 0x1000, 0x4000, 0x8000)
				GROUP BY BSL.appId
				) T
		WHERE	BSL.appId = T.appId
				AND BSL.jobId = MaxFullJobID)/@ONE_MB
END
--------------------------- END BaseLineSizeMB MODS -------------------------------------------------------


UPDATE	S 
SET		StoreName = I.SIDBStoreName,
		TotalDataSizeMB = I.TotalDataSizeBytes/1024/1024, 
		InstancedDataSizeMB = I.InstancedDataSizeBytes/1024/1024, 
		createdTime = dbo.GetDateTime(I.CreatedTime)
FROM	#tmpStores S
		INNER JOIN IdxSIDBStore I WITH (NOLOCK) ON S.StoreId = I.SIDBStoreId

UPDATE  #tmpStores
SET		LastRecordedTime = dbo.UTCToLocalTime(LastRecordedTime, @timzonename) ,
		CreatedTime = dbo.UTCToLocalTime(CreatedTime, @timzonename)


-- Begin of calculation for FET --
IF (@releaseId < 16) OR (@releaseId = 16 AND @csSPVersion < 15)
BEGIN

IF OBJECT_ID('tempdb.dbo.#DDBAppSize') IS NOT NULL DROP TABLE #DDBAppSize
CREATE	TABLE #DDBAppSize (SIDBStoreId INT, clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT, 
		fullCycleNum INT, appSize FLOAT, bArchiver INT, bMultiInstances INT)

--get the last full size if not a archiver and not a Turbo subclient
--get the SUM of archiver without any time range
INSERT	INTO #DDBAppSize
SELECT	SIDBStoreId, clientId, appTypeId, instance, backupset, appId, fullCycleNum, SUM(totalUncompBytes), bArchiver, 0
FROM	APP_Application A WITH(NOLOCK)
	INNER JOIN (
	SELECT	SIDBStoreId, B.appId, B.fullCycleNum, B.totalUncompBytes, ISNULL(H.isDMAppType, 0) AS bArchiver,
			ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, AF.appId ORDER BY B.servStartDate DESC) AS rowId
	FROM	archFileCopyDedup AFD WITH(NOLOCK) 
		INNER JOIN archFile AF WITH(NOLOCK) ON AFD.archFileId = AF.id AND AFD.commCellId = AF.commCellId AND AF.isValid = 1
		INNER JOIN JMBkpStats B WITH(NOLOCK) ON AF.jobId = B.jobId AND AF.commCellId = B.commCellId 
		LEFT OUTER JOIN APP_IDATypeHandlingInfo H WITH(NOLOCK) ON B.appType = H.appTypeId
	WHERE	B.status IN (1, 3, 14) AND (B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768) OR ISNULL(H.isDMAppType, 0) = 1)
		AND B.appType <> 106 AND B.dataStatus = 0 AND B.totalUncompBytes > 0
		AND AFD.SIDBStoreId > 0
	) T ON A.id = T.appId
WHERE	(T.rowId = 1 OR bArchiver > 0) AND A.subclientStatus&2 = 0
GROUP BY SIDBStoreId, clientId, appTypeId, instance, backupset, appId, fullCycleNum, bArchiver
		
IF OBJECT_ID('tempdb.dbo.#TurboSubclient') IS NOT NULL DROP TABLE #TurboSubclient
CREATE	TABLE #TurboSubclient (appId INT)

IF (@releaseId < 15)
	INSERT	INTO #TurboSubclient
	SELECT	id
	FROM	APP_Application WITH(NOLOCK)
	WHERE	appTypeId IN (SELECT componentNameID FROM APP_IDAProp WITH(NOLOCK) WHERE attrName = 'Enable Archive' AND attrVal = '1' AND modified = 0)
ELSE
	INSERT	INTO #TurboSubclient
	SELECT	id
	FROM	APP_Application WITH(NOLOCK) 
	WHERE	id IN (SELECT componentNameId FROM APP_subclientProp WITH(NOLOCK) 
			WHERE attrName = 'Is Turbo Subclient' and cs_attrName = CHECKSUM(N'Is Turbo Subclient') and attrval = '1' and modified = 0)
		AND id IN (SELECT componentNameId FROM APP_subclientProp WITH(NOLOCK) 
			WHERE attrName = 'First Turbo Job id' and cs_attrName = CHECKSUM(N'First Turbo Job id') and attrval != '0' and modified = 0)
		AND appTypeId IN (
			SELECT	type 
			FROM	APP_iDAType WITH(NOLOCK) 
			WHERE	type < 600 AND type NOT IN (28,86) 
				AND (name LIKE '%file%system%' OR type IN (13,54,22,80)
					OR type IN (SELECT DISTINCT appTypeId FROM simAppTypeLicTypeMap WITH(NOLOCK) WHERE display_name = 'File System'))
			)
	UNION
	SELECT	id
	FROM	APP_Application WITH(NOLOCK) 
	WHERE	(subclientStatus & 0x10000) > 0

--get the SUM of the last full cycle for Turbo subclients. #DDBAppSize only has the latest fullCycleNum for each subclient.
UPDATE S SET appSize = T.totalUncompBytes, bArchiver = 1
FROM	#DDBAppSize S
	INNER JOIN (
		SELECT	A.SIDBStoreId, A.appId, SUM(B.totalUncompBytes) AS totalUncompBytes
		FROM	#DDBAppSize A
			INNER JOIN #TurboSubclient TS ON A.appId = TS.appId
			INNER JOIN JMBkpStats B WITH(NOLOCK) ON A.appId = B.appId AND A.fullCycleNum = B.fullCycleNum
		WHERE	B.status IN (1, 3, 14) AND B.dataStatus = 0 AND B.totalUncompBytes > 0
		GROUP BY A.SIDBStoreId, A.appId
		) T ON S.SIDBStoreId = T.SIDBStoreId AND S.appId = T.appId

DROP TABLE #TurboSubclient


--Exclude DDB, Index and SILO subclients
DELETE S FROM #DDBAppSize S 
	INNER JOIN APP_subclientProp WITH(NOLOCK) ON appId = componentNameId
WHERE  attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') AND attrVal <> '0' AND modified = 0
AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'))

--Exclude (DM2)SQL Backup run on the WeBServer Client.
DELETE S FROM #DDBAppSize S 
	INNER JOIN APP_subclientProp SP WITH(NOLOCK) ON S.appId = SP.componentNameId AND SP.attrName = 'DM2' 
		AND SP.cs_attrName = CHECKSUM(N'DM2') AND SP.attrVal <> '0' AND SP.modified = 0
	INNER JOIN (SELECT clientId FROM simInstalledPackages WITH(NOLOCK) WHERE simPackageID = 803) P ON S.clientId = P.ClientId
	INNER JOIN (SELECT componentNameId FROM APP_ClientProp WITH(NOLOCK) 
				WHERE attrname LIKE '%DM2WebService%' AND attrVal <> '0' AND modified = 0
				) C ON S.ClientId = C.componentNameId


-- For Oracle & SAP,Informix Database count largest subclient for each instance
DELETE	S
FROM	#DDBAppSize S INNER JOIN (
		SELECT	SIDBStoreId, clientId, appTypeId, instanceId, MAX(appSize) AS maxSize
		FROM	#DDBAppSize
		WHERE	appTypeId IN (3,5,22,61,79,80,104,128) AND bArchiver = 0
		GROUP BY SIDBStoreId, clientId, appTypeId, instanceId
		) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId AND S.instanceId = T.instanceId
WHERE	appSize < maxSize

-- For DB2, count largest subclient for each backupset
DELETE	S
FROM	#DDBAppSize S INNER JOIN (
		SELECT	SIDBStoreId, clientId, appTypeId, instanceId, backupsetId, MAX(appSize) AS maxSize
		FROM	#DDBAppSize
		WHERE	appTypeId IN (37,62,103,135) AND bArchiver = 0
		GROUP BY SIDBStoreId, clientId, appTypeId, instanceId, backupsetId
		) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId AND S.instanceId = T.instanceId AND S.backupsetId = T.backupsetId
WHERE	appSize < maxSize

--Only count the default instance of non-DB agents
DELETE	S
FROM	#DDBAppSize S
	INNER JOIN APP_InstanceName I WITH(NOLOCK) ON S.instanceId = I.id
	INNER JOIN (
		SELECT	SIDBStoreId, clientId, appTypeId, COUNT(DISTINCT instanceId) AS nInstance
		FROM	#DDBAppSize
		WHERE	appTypeId NOT IN (3,5,22,37,61,62,79,80,103,104,128,135) AND bArchiver = 0
		GROUP BY SIDBStoreId, clientId, appTypeId
		HAVING	COUNT(DISTINCT instanceId) > 1
		) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId
WHERE	I.name NOT LIKE 'defaultInstance%'

-- Multiple backupset of same non-DB agent, count only the biggest backupset and ignore others.
IF OBJECT_ID('tempdb.dbo.#BackupSetSize') IS NOT NULL DROP TABLE #BackupSetSize
CREATE TABLE #BackupSetSize (SIDBStoreId INT, clientId INT, appTypeId INT, backupSetId INT, appSize BIGINT)

INSERT INTO #BackupSetSize
SELECT	SIDBStoreId, clientId, appTypeId, backupSetId, SUM(appSize)
FROM	#DDBAppSize
WHERE	appTypeId NOT IN (3,5,22,37,61,62,79,80,103,104,128,135) AND bArchiver = 0
GROUP BY SIDBStoreId, clientId, appTypeId, backupSetId

DELETE	S
FROM	#DDBAppSize S INNER JOIN (
		SELECT	SIDBStoreId, clientId, appTypeId, backupSetId, 
				ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, clientId, appTypeId, backupSetId ORDER BY appSize DESC) AS rowId
		FROM	#BackupSetSize
		) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId AND S.backupSetId = T.backupSetId
WHERE	rowId > 1

DROP TABLE #BackupSetSize

-- For DAG clients, count largest DAG client per DAG group when [ExDAGCombineLicense] flag is enabled in GxGlobalParam
IF (@releaseId >= 15)
BEGIN
	IF OBJECT_ID('tempdb.dbo.#DAGClients') IS NOT NULL DROP TABLE #DAGClients
	CREATE TABLE #DAGClients (SIDBStoreId INT, DAGName VARCHAR(MAX), clientId INT, size BIGINT)

	SET @nstring = N'
	IF EXISTS (SELECT * FROM GxGlobalParam WITH(NOLOCK) WHERE name =''ExDAGCombineLicense'' AND value = ''1'' AND modified = 0)
		INSERT INTO #DAGClients
		SELECT	SIDBStoreId, attrVal, S.clientId, SUM(appSize)
		FROM	#DDBAppSize S
				INNER JOIN APP_ClientProp P WITH(NOLOCK)
				ON S.clientId = P.componentNameId AND attrName = ''DAG Name'' AND modified = 0
		GROUP BY SIDBStoreId, attrVal, S.clientId'

	EXEC sp_executesql @nstring

	DELETE	#DAGClients
	FROM	#DAGClients D INNER JOIN 
			(SELECT SIDBStoreId, DAGName, clientId, size, 
					ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, DAGName ORDER BY size DESC) AS rowId
			FROM	#DAGClients
			) T ON D.SIDBStoreId = T.SIDBStoreId AND D.DAGName = T.DAGName
	WHERE	rowId = 1

	UPDATE	S SET appSize = 0
	FROM	#DDBAppSize S INNER JOIN #DAGClients D
		ON	S.SIDBStoreId = D.SIDBStoreId AND S.clientId = D.clientId AND S.appTypeId <> 106

	DROP TABLE #DAGClients
END


--get legal hold jobs
IF OBJECT_ID('tempdb.dbo.#LegalHoldJob') IS NOT NULL DROP TABLE #LegalHoldJob
CREATE TABLE #LegalHoldJob (jobId INT, commCellId INT, appSize BIGINT)

INSERT INTO #LegalHoldJob
SELECT	A.jobId, A.commCellId, A.totalBackupSize
FROM	JMAdminJobStatsTable A WITH(NOLOCK)
		INNER JOIN (
		SELECT	jobId, commCellId, CAST(SUBSTRING(xmlJobInfo, CHARINDEX('>', xmlJobInfo)+1, LEN(xmlJobInfo)) AS XML) AS xmlJobInfo
		FROM	JMJobStats WITH(NOLOCK)
		WHERE	opType = 64
		) T ON A.jobId = T.jobId AND A.commCellId = T.commCellId
WHERE	A.status in (1, 3, 14) AND A.dataStatus = 0
	AND	T.xmlJobInfo.value('(JobManager_AdminJobDetailsXml/compPolicyJobInfo/compliancePolicyInfo/@phaseDetails)[1]', 'INT') & 0x10 <> 0

UPDATE	S SET appSize = CASE WHEN S.bArchiver = 0 THEN T.appSize ELSE (S.appSize+T.appSize) END 
FROM	#DDBAppSize S
	INNER JOIN (
	SELECT	SIDBStoreId, appId, SUM(appSize) AS appSize
	FROM	(SELECT	DISTINCT SIDBStoreId, AF.appId, LH.jobId, LH.commCellId, LH.appSize
			FROM	archFileCopyDedup AFD WITH(NOLOCK) 
				INNER JOIN archFile AF WITH(NOLOCK) ON AFD.archFileId = AF.id AND AFD.commCellId = AF.commCellId AND AF.isValid = 1
				INNER JOIN #LegalHoldJob LH ON AF.jobId = LH.jobId AND AF.commCellId = LH.commCellId
		WHERE	AFD.SIDBStoreId > 0
			) L
	GROUP BY SIDBStoreId, appId
	) T ON S.SIDBStoreId = T.SIDBStoreId and S.appId = T.appId

DROP TABLE #LegalHoldJob


-- VSA jobs: count unique VM size in last FULL/Synthetic FULL jobs of each VSA subclient
IF OBJECT_ID('tempdb.dbo.#DDBVSAJobs') IS NOT NULL DROP TABLE #DDBVSAJobs
CREATE TABLE #DDBVSAJobs (SIDBStoreId INT, VMclientId INT, jobId INT, commCellId INT, size BIGINT, bArchiver INT)

INSERT	INTO #DDBVSAJobs
SELECT	SIDBStoreId, VMclientId, jobId, commCellId, 0, 0
FROM	(
	SELECT	SIDBStoreId, VMclientId, VM.jobId, VM.commCellId,
			ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, VMclientId ORDER BY B.servStartDate DESC) AS rowId
	FROM	archFileCopyDedup AFD WITH(NOLOCK) 
		INNER JOIN archFile AF WITH(NOLOCK) ON AFD.archFileId = AF.id AND AFD.commCellId = AF.commCellId AND AF.isValid = 1
		INNER JOIN JMBkpStats B WITH(NOLOCK) ON AF.jobId = B.jobId AND AF.commCellId = B.commCellId 
		INNER JOIN APP_VMProp VM WITH(NOLOCK) ON B.jobId = VM.jobId AND B.commCellId = VM.commCellId 
			AND attrName = 'vmStatus' AND attrVal = '0'
	WHERE	B.status IN (1, 3, 14) AND B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
		AND B.dataStatus = 0 AND B.totalUncompBytes > 0
		AND AFD.SIDBStoreId > 0
	) T 
WHERE	T.rowId = 1 
GROUP BY SIDBStoreId, VMclientId, jobId, commCellId

CREATE INDEX #DDBVSAJobs_jobId_commCellId ON #DDBVSAJobs (jobId, commCellId, VMclientId) INCLUDE (SIDBStoreId)

IF OBJECT_ID('tempdb.dbo.##VMSize') IS NOT NULL DROP TABLE #VMSize
CREATE TABLE #VMSize (jobId INT, commCellId INT, VMclientId INT, vmStubStatus INT, vmSize FLOAT)

INSERT	INTO #VMSize
SELECT	jobId, commCellId, VMClientId, ISNULL([vmStubStatus], 0), ISNULL([vmUsedSpace], [vmGuestSize])
FROM	(SELECT V.jobId, V.commcellId, V.VMclientId, attrName, attrVal
		FROM	APP_VMProp V WITH(NOLOCK) 
				INNER JOIN #DDBVSAJobs T ON V.jobId = T.jobId AND V.commCellId = T.commCellId AND V.VMclientId = T.VMclientId
		WHERE	attrName IN ('vmUsedSpace', 'vmGuestSize', 'vmStubStatus')
		) S
		PIVOT	(MAX(attrVal) FOR attrName IN ([vmUsedSpace], [vmGuestSize], [vmStubStatus])) AS P

INSERT	INTO #DDBAppSize
SELECT	SIDBStoreId, J.VMclientId, 106, 0, 0, 0, 0, U.vmSize, CASE WHEN U.vmStubStatus <> 0 THEN 1 ELSE 0 END, 0
FROM	#DDBVSAJobs J
	INNER JOIN #VMSize U ON J.jobId = U.jobId AND J.commCellId = U.commCellId AND J.VMclientId = U.VMclientId

DROP TABLE #DDBVSAJobs
DROP TABLE #VMSize

UPDATE	S SET FrontendBackupMB = appSizeMB
FROM	#tmpStores S INNER JOIN 
		(SELECT SIDBStoreId, SUM(appSize)/1024/1024 AS appSizeMB FROM #DDBAppSize WHERE bArchiver = 0 GROUP BY SIDBStoreId
		) D ON S.StoreId = D.SIDBStoreId

UPDATE	S SET FrontendArchiveMB = appSizeMB
FROM	#tmpStores S INNER JOIN 
		(SELECT SIDBStoreId, SUM(appSize)/1024/1024 AS appSizeMB FROM #DDBAppSize WHERE bArchiver = 1 GROUP BY SIDBStoreId
		) D ON S.StoreId = D.SIDBStoreId

DROP TABLE #DDBAppSize

END
-- End of calculation for FET --

-- Begin of calculation for dedupe ratio --

CREATE TABLE #StoreDedupe (SIDBStoreId INT, SizeOnMediaMB BIGINT, AppSizeMB BIGINT, AppSizeDelMB BIGINT, DedupeRatio FLOAT)

INSERT	INTO #StoreDedupe
SELECT	SIDBStoreId, SUM(CAST(PhysicalBytesMB AS BIGINT)), 0, 0, 0
FROM	MMVolume V WITH (NOLOCK) 
WHERE	SIDBStoreId > 0 AND Attributes&512 = 0 AND SiloStatus <> 3
GROUP BY SIDBStoreId

CREATE TABLE #StoreAppSize (SIDBStoreId INT, AppSizeMB BIGINT)
CREATE TABLE #CopyStoreJob (jobId INT, commCellId INT, copyId INT, SIDBStoreId INT, physicalSize BIGINT)

IF COL_LENGTH('archFileCopy','unCompSize') IS NULL
BEGIN
	INSERT INTO #CopyStoreJob
	SELECT	AF.jobId, AF.commCellId, AFD.archCopyId, AFD.SIDBStoreId, SUM(AFC.physicalSize)
	FROM	archFile AF WITH (NOLOCK)
			INNER JOIN archFileCopyDedup AFD WITH (NOLOCK) ON AF.id = AFD.archFileId AND AF.commCellId = AFD.commCellId AND AF.isValid = 1
			INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AFD.archFileId = AFC.archFileId AND AFD.commCellId = AFC.commCellId AND AFC.archCopyId = AFD.archCopyId
	WHERE	AFD.SIDBStoreId > 0
	GROUP BY AF.jobId, AF.commCellId, AFD.archCopyId, AFD.SIDBStoreId

	CREATE CLUSTERED INDEX CopyStoreJob_idx ON #CopyStoreJob (SIDBStoreId, physicalSize)

	INSERT	INTO #StoreAppSize
	SELECT	J.SIDBStoreId, SUM((1.0*J.physicalSize/B.totalBackupSize)*B.totalUncompBytes)/1024/1024
	FROM	#CopyStoreJob J
			INNER JOIN JMBkpStats B WITH (NOLOCK)
				ON J.jobId = B.jobId AND J.commCellId = B.commCellId
	WHERE	B.totalBackupSize > 0
	GROUP BY J.SIDBStoreId

	INSERT	INTO #StoreAppSize
	SELECT	J.SIDBStoreId, SUM(J.physicalSize)/1024/1024
	FROM	#CopyStoreJob J
			INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK)
				ON J.jobId = A.jobId AND J.commCellId = A.commCellId
	GROUP BY J.SIDBStoreId
END
ELSE
BEGIN
	SET @nstring = N'
	INSERT	INTO #StoreAppSize
	SELECT  AFD.SIDBStoreId, SUM(AFC.unCompSize)/1024/1024
	FROM    archFileCopyDedup AFD WITH(NOLOCK)
		INNER JOIN archFileCopy AFC WITH(NOLOCK) 
		ON AFC.archFileId = AFD.archFileId AND AFC.commCellId = AFD.commCellId AND AFC.archCopyId = AFD.archCopyId 
	GROUP BY AFD.SIDBStoreId'

	EXEC sp_executesql @nstring
END

UPDATE	S SET AppSizeMB = T.AppSizeMB
FROM	#StoreDedupe S
		INNER JOIN (
			SELECT	SIDBStoreId, SUM(AppSizeMB) AS AppSizeMB
			FROM	#StoreAppSize
			GROUP BY SIDBStoreId
		) T ON S.SIDBStoreId = T.SIDBStoreId
		
IF (@releaseId >= 15)
BEGIN
	SET @nstring = N'
	UPDATE	S SET AppSizeDelMB = T.AppSizeDelMB
	FROM	#StoreDedupe S
			INNER JOIN (
				SELECT	SIDBStoreId, SUM(ISNULL(appSizeFreedBytes,0))/1024/1024 AS AppSizeDelMB
				FROM	MMDeletedAF WITH (NOLOCK)
				WHERE	SIDBStoreId > 0
				GROUP BY SIDBStoreId
			) T ON S.SIDBStoreId = T.SIDBStoreId'

	EXEC sp_executesql @nstring
END

UPDATE	#StoreDedupe SET DedupeRatio = 1.0*SizeOnMediaMB/(AppSizeMB + AppSizeDelMB)
WHERE	(AppSizeMB + AppSizeDelMB) > 0

DROP TABLE #CopyStoreJob
DROP TABLE #StoreAppSize

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IdxSIDBStore' AND COLUMN_NAME = 'ExtendedFlags')--ExtendedFlags column was added in v11 SP14
BEGIN
	SET @nstring = N'
	UPDATE	S 
	SET 	ExtendedFlags = I.ExtendedFlags
	FROM	#tmpStores S
		INNER JOIN IdxSIDBStore I WITH (NOLOCK) ON S.StoreId = I.SIDBStoreId'

	EXEC sp_executesql @nstring
END

-- END of calculation of dedupe ratio --


SET @ddbperformance = (
	SELECT	AGC.id as '@CopyId',
			StoreId as '@StoreId',
			StoreName as '@StoreName',
			MediaAgentNames as '@MediaAgentNames',
			CreatedTime as '@CreatedTime',
			LastRecordedTime as '@LastRecordedTime',
			AvgQITime as '@AvgQITime',
			PrimaryRecords as '@PrimaryRecords',
			SecondaryRecords as '@SecondaryRecords',
			ZeroRefRecords as '@ZeroRefRecords',
			TotalDataSizeMB as '@TotalDataSizeMB',
			InstancedDataSizeMB as '@InstancedDataSizeMB',
			DDBSizeMB as '@DDBSizeMB',
			FrontendBackupMB as '@FrontendBackupMB',
			FrontendArchiveMB as '@FrontendArchiveMB',
			BaseLineSizeMB as '@BaseLineSizeMB',
			Readiness AS '@Readiness',
			ISNULL(AppSizeMB, 0) AS '@AppSizeMB',
			ISNULL(DedupeRatio, 0) AS '@DedupeRatio',
			T.ExtendedFlags AS '@ExtendedFlags',
			CASE WHEN U.SIDBStoreID IS NULL THEN 0 ELSE 1 END AS '@NeedUpgrade',
			CAST( (
					SELECT	SubStoreId as '@SubStoreId',
							AccessPath as '@AccessPath',
							TS.PrimaryRecords as '@PrimaryRecords',
							TS.SecondaryRecords as '@SecondaryRecords',
							TS.ZeroRefRecords as '@ZeroRefCount',
							TS.LastRecordedTime as '@LastRecordedTime',
							TS.AvgQITime as '@AvgQITime',
							partitionSize as '@PartitionSize',
							TS.MediaAgentName as '@MediaAgentName'
					FROM	#tmpSubStores TS
					WHERE TS.StoreId = T.StoreId
					FOR XML PATH ('PartitionInfo')
				) AS XML 
			)		
	FROM	#tmpStores T
			INNER JOIN archCopySIDBStore CS WITH (NOLOCK) ON T.StoreId = CS.SIDBStoreId
			INNER JOIN archGroupCopy AGC WITH (NOLOCK) 
				ON CS.CopyId = AGC.id AND AGC.dedupeFlags&(262144|134217728) = 262144 AND AGC.type <> 5 -- CVA_AGCOPYTYPE_TRANSITIVE
			LEFT OUTER JOIN #StoreDedupe S 
				ON T.StoreId = S.SIDBStoreId
			LEFT OUTER JOIN #upgradeTov5DDBs U
				ON T.StoreId = U.SIDBStoreID
	FOR XML PATH ('Metric')
	)
IF @STANDALONE > 0
   Select * from #tmpStores


SET @surveyXML = (
	SELECT
	@ddbperformance
	FOR XML PATH ('DDBPerformance') 
	-- Replace <<RESULTS_NAME>> with specific name
)
IF OBJECT_ID('tempdb..#tmpSubStores') IS NOT NULL DROP TABLE #tmpSubStores
IF OBJECT_ID('tempdb..#tmpStores') IS NOT NULL DROP TABLE #tmpStores
IF OBJECT_ID('tempdb..#StoreDedupe') IS NOT NULL DROP TABLE #StoreDedupe
IF OBJECT_ID('tempdb..#SIAppID') IS NOT NULL DROP TABLE #SIAppID		-- MR 172535
IF object_id('tempdb.dbo.#upgradeTov5DDBs') is not null DROP TABLE #upgradeTov5DDBs
--------- END SURVEY QUERY ---------

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

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')
)

--SELECT cast (@surveyXML as XML)
IF @STANDALONE > 0
  SELECT @ddbperformance
SET NOCOUNT OFF

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