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

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

SET NOCOUNT ON

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

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



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

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

/*
For a commcell ID it provides all SP name, DB Store name, SP Copy Name their corresponding SizeOfApp, SizeOnMedia and BaseLineSize with records having non-zero SizeOfApp and SizeOnMedia
*/

IF object_id('tempdb.dbo.#TempStoreInfo') is not null
DROP TABLE #TempStoreInfo

CREATE TABLE #TempStoreInfo (SIDBStoreId INT, AppSize FLOAT, MediaSize FLOAT, Baseline FLOAT)

INSERT	INTO #TempStoreInfo
SELECT	S.SIDBStoreId, 0, SUM(CAST(V.PhysicalBytesMB AS FLOAT))*1024*1024, 0
FROM	IdxSIDBStore S WITH(NOLOCK)
	INNER JOIN MMVolume V WITH(NOLOCK) ON V.SIDBStoreId = S.SIDBStoreId 
WHERE	S.SIDBStoreId > 0 AND S.SealedTime = 0 AND V.PhysicalBytesMB > 0 AND V.SiloStatus <> 3 AND (V.Attributes & 512) = 0
GROUP BY S.SIDBStoreId

UPDATE	#TempStoreInfo SET AppSize = T.AppSize
FROM	(
	SELECT	AFD.SIDBStoreId, SUM((1.0*AFC.physicalSize/J.totalBackupSize)*J.totalUncompBytes) AS AppSize
	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 
		INNER JOIN archFile AF WITH(NOLOCK) 
			ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AF.isValid = 1
		INNER JOIN JMBkpStats J WITH(NOLOCK) 
			ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId AND J.status in (1, 3, 14) AND J.totalBackupSize > 0
		INNER JOIN #TempStoreInfo S ON AFD.SIDBStoreId = S.SIDBStoreId
	GROUP BY AFD.SIDBStoreId
	) T 
WHERE	#TempStoreInfo.SIDBStoreId = T.SIDBStoreId

UPDATE	#TempStoreInfo SET AppSize += T.AppSize
FROM	(
	SELECT	AFD.SIDBStoreId, SUM(CAST(AFC.physicalSize AS FLOAT)) AS AppSize
	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 
		INNER JOIN archFile AF WITH(NOLOCK) 
			ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AF.isValid = 1
		INNER JOIN JMAdminJobStatsTable J WITH(NOLOCK) 
			ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId AND J.opType = 11 AND J.status in (1, 3, 14) AND J.totalBackupSize > 0
		INNER JOIN #TempStoreInfo S ON AFD.SIDBStoreId = S.SIDBStoreId
	GROUP BY AFD.SIDBStoreId
	) T 
WHERE	#TempStoreInfo.SIDBStoreId = T.SIDBStoreId

-- Calculate Baseline
CREATE TABLE #Subclient (SIDBStoreId INT, AppId INT, MaxFullJobID INT, FullCycleNum INT, FullSize BIGINT)

IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL
BEGIN
	INSERT	INTO #Subclient
	SELECT	CSD.SIDBStoreId, J.appId, MAX(J.jobId), 0, 0
	FROM	JMJobDataStats J WITH(NOLOCK) 
			INNER JOIN archSubclientCopyDDBMap CSD WITH(NOLOCK) ON J.appId = CSD.appId AND J.archGrpCopyId = CSD.copyId
	WHERE	J.dataType = 1 AND J.status = 100 AND J.disabled&256 = 0
	GROUP BY CSD.SIDBStoreId, J.appId
END
ELSE
BEGIN
	INSERT	INTO #Subclient
	SELECT	AGC.SIDBStoreId, J.appId, MAX(J.jobId), 0, 0
	FROM	JMJobDataStats J WITH(NOLOCK) 
			INNER JOIN archGroupCopy AGC WITH(NOLOCK) ON J.archGrpCopyId = AGC.id 
	WHERE	J.dataType = 1 AND J.status = 100 AND J.disabled&256 = 0 AND AGC.SIDBStoreId > 0
	GROUP BY AGC.SIDBStoreId, J.appId
END

UPDATE	S SET FullCycleNum = J.fullCycleNum
FROM	#Subclient S
		INNER JOIN JMBkpStats J WITH(NOLOCK) ON S.MaxFullJobID = J.jobId AND S.AppId = J.appId

UPDATE	S SET FullSize = J.totalUncompBytes
FROM	#Subclient S
		INNER JOIN JMBkpStats J WITH(NOLOCK) ON S.AppId = J.appId AND S.FullCycleNum = J.fullCycleNum 
WHERE	J.bkpLevel IN (1, 0x40, 0x80, 0x400, 0x1000, 0x4000, 0x8000) AND J.status IN (1, 3, 14)

UPDATE	S SET fullCycleNum = J.fullCycleNum
FROM	#Subclient S
		INNER JOIN JMAdminJobStatsTable J WITH(NOLOCK) ON S.MaxFullJobID = J.jobId AND S.AppId = J.appId

UPDATE	S SET FullSize = J.totalBackupSize
FROM	#Subclient S
		INNER JOIN JMAdminJobStatsTable J WITH(NOLOCK) ON S.AppId = J.appId AND S.FullCycleNum = J.fullCycleNum 
WHERE	J.ER_BkpLevel IN (1, 0x40, 0x80, 0x400, 0x1000, 0x4000, 0x8000) AND J.status IN (1, 3, 14)

UPDATE	T SET BaseLine = S.BaseLine
FROM	#TempStoreInfo T
		INNER JOIN 
		(SELECT	SIDBStoreId, SUM(FullSize) AS BaseLine
		FROM	#Subclient
		GROUP BY SIDBStoreId
		) S ON T.SIDBStoreId = S.SIDBStoreId

DROP TABLE #Subclient

SET @surveyXML = (
	SELECT DISTINCT 
		I.SIDBStoreId AS 'SIDBStrId',
		AG.id  AS 'PolicyId',
		AGC.id AS 'CopyId',
		dbo.NormalizeForXML(AG.name) AS 'SPName',
		dbo.NormalizeForXML(I.SIDBStoreName) AS 'SIDBStoreAliasName',
		dbo.NormalizeForXML(AGC.name) AS 'StoragePolicyCpy',
		ISNULL(AppSize,0)   AS 'totalAppSize',
		ISNULL(MediaSize,0) AS 'sizeOnMedia',
		ISNULL(Baseline*1.2, 0) AS 'BaselineSize'
	FROM
		IdxSIDBStore I WITH(NOLOCK)
		INNER JOIN archCopySIDBStore CS WITH(NOLOCK) ON I.SIDBStoreId = CS.SIDBStoreId
		INNER JOIN archGroupCopy AGC WITH(NOLOCK) ON AGC.id = CS.CopyId AND (AGC.dedupeFlags & 134217728) = 0
		INNER JOIN archGroup AG WITH(NOLOCK) ON AG.id = AGC.archGroupId
		LEFT OUTER JOIN #TempStoreInfo T ON I.SIDBStoreId = T.SIDBStoreId
	WHERE
		I.SIDBStoreId > 0 AND I.SealedTime = 0
	FOR XML RAW('SIDBStrIdBlS'), ROOT('TblSIDBStoreAndBaselineSize')
	-- Replace <<RESULTS_NAME>> with specific name
)

SET @surveyXML= ISNULL(@surveyXML,'<TblSIDBStoreAndBaselineSize />')
DROP TABLE #TempStoreInfo	

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

SET NOCOUNT OFF

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