--Name :- Backup Data retention
--Description :- Backup Data retention
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

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

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

--------- BEGIN SURVEY QUERY ---------
--------- Insert your SQL statements here 


-- Return total sizes of data written on media that will age in 30 days, 30-90 days, 90-365 days and after 365 days.
-- Considering basic retention and extended retention rules and manully pinnrd jobs.
-- Ignore retention rules for backup cycle. Ignore chaining between data and log backup jobs. Ignore job counts.
-- Because of deduplication, the amount of data size for each time range will be different from the amount of reusable space on media.

DECLARE	@today	INT = dbo.GetUnixTime(GETUTCDATE())
DECLARE	@30day	INT = @today + 30*24*3600
DECLARE	@90day	INT = @today + 90*24*3600
DECLARE	@365day	INT = @today + 365*24*3600


CREATE TABLE #FiveDaysSize (copyId INT, nFiveDays INT, DataWritten BIGINT)

--------------------------------------------
-- Calculate dedup ratio of dedupe copies --
--------------------------------------------
CREATE TABLE #DedupeCopy (
    archGrpCopyId INT, activeStoreId INT, dedupeFlags INT, 
    dataRead BIGINT, dataWritten BIGINT, dedupRatio FLOAT, dedupSaving DECIMAL(10,2)
	)

CREATE TABLE #CommCell (commCellId INT)

INSERT INTO #CommCell
SELECT 2 UNION
SELECT DISTINCT origCCcommCellID FROM MMMedia WHERE (Attributes & 268435456) > 0 AND origCCcommCellID > 2

IF (COL_LENGTH('RptStorageUsage', 'allAppSize') IS NOT NULL)
BEGIN
	SET @sqlstr = N'
	DECLARE @MaxDate DATETIME = (SELECT MAX(startDT) FROM RptStorageUsage WITH (NOLOCK) WHERE type = 1)

	INSERT	INTO #DedupeCopy
	SELECT	copyId, 0, 0, SUM(allAppSize), SUM(allMediaSize), MAX(dedupRatio), 0
	FROM	RptStorageUsage U WITH (NOLOCK)
	WHERE	startDT = @MaxDate AND type = 1 AND dedupRatio < 1
	GROUP BY copyId'

	EXEC sp_executesql @sqlstr
END
ELSE
BEGIN
	IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL
	BEGIN
		INSERT	INTO #DedupeCopy
		SELECT	id, CS.SIDBStoreId, dedupeFlags, 0, 0, 0, 0
		FROM	archGroupCopy AGC WITH (NOLOCK)
				INNER JOIN archCopySIDBStore CS WITH (NOLOCK) ON AGC.id = CS.copyId AND ((CS.flags & 6) = 6) /*IDX_COPY_DDB_DEFAULT = 2, IDX_COPY_DDB_ACTIVE = 4)*/
		WHERE	((AGC.dedupeFlags & 262144) > 0)
	END
	ELSE
	BEGIN
		INSERT	INTO #DedupeCopy
		SELECT	id, CS.SIDBStoreId, dedupeFlags, 0, 0, 0, 0
		FROM	archGroupCopy AGC WITH (NOLOCK)
				INNER JOIN archCopySIDBStore CS WITH (NOLOCK) ON AGC.id = CS.copyId
				INNER JOIN IdxSIDBStore SS WITH (NOLOCK) ON CS.SIDBStoreId = SS.SIDBStoreId AND SS.SealedTime = 0
		WHERE	((AGC.dedupeFlags & 262144) > 0)
	END

	-- Total Application Size of fully copied jobs on each copies
	CREATE TABLE #CopyReadSize (archGrpCopyId INT, dataRead BIGINT)

	INSERT	INTO #CopyReadSize
	SELECT	J.archGrpCopyId, SUM(B.totalUncompBytes)
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN JMJobDataStats J ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 1
			INNER JOIN #DedupeCopy C ON J.archGrpCopyId = C.archGrpCopyId
			INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
	WHERE	J.archGrpId = B.dataArchGrpId AND J.status IN (100, 102, 103)
	GROUP BY J.archGrpCopyId

	INSERT	INTO #CopyReadSize
	SELECT	J.archGrpCopyId, SUM(B.totalUncompBytes)
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN JMJobDataStats J ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 4
			INNER JOIN #DedupeCopy C ON J.archGrpCopyId = C.archGrpCopyId
			INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
	WHERE	J.archGrpId = B.logArchGrpId AND B.dataBackedUp = 0 AND J.status IN (100, 102, 103)
	GROUP BY J.archGrpCopyId

	INSERT	INTO #CopyReadSize
	SELECT	J.archGrpCopyId, SUM(A.totalBackupSize)
	FROM	JMAdminJobStatsTable A WITH (NOLOCK)
			INNER JOIN JMJobDataStats J ON J.jobId = A.jobId AND J.commCellId = A.commCellId AND J.dataType = 1
			INNER JOIN #DedupeCopy C ON J.archGrpCopyId = C.archGrpCopyId
			INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
	GROUP BY J.archGrpCopyId

	-- Partially copied jobs
	CREATE TABLE #PartialJobSize (jobId INT, commCellId INT, archGrpCopyId INT, 
			totalAppSize BIGINT, totalBackupSize BIGINT, dataRead BIGINT)

	INSERT	INTO #PartialJobSize
	SELECT	J.jobId, J.commCellId, J.archGrpCopyId, 0, 0, 0
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #DedupeCopy C ON J.archGrpCopyId = C.archGrpCopyId
			INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
	WHERE	J.status <> 1000
	GROUP BY J.jobId, J.commCellId, J.archGrpCopyId
	HAVING MAX(J.status) IN (102, 103) OR MIN(J.status) = 100 AND MAX(J.status) = 101

	UPDATE	#PartialJobSize
	SET		totalAppSize = J.totalUncompBytes, totalBackupSize = J.totalBackupSize
	FROM	JMBkpStats J WITH (NOLOCK)
	WHERE	#PartialJobSize.jobId = J.jobId AND #PartialJobSize.commCellId = J.commCellId

	UPDATE	#PartialJobSize
	SET		totalAppSize = J.totalBackupSize, totalBackupSize = J.totalBackupSize
	FROM	JMAdminJobStatsTable J WITH (NOLOCK)
	WHERE	#PartialJobSize.jobId = J.jobId AND #PartialJobSize.commCellId = J.commCellId

	UPDATE	#PartialJobSize
	SET		dataRead = CASE WHEN totalBackupSize > 0 THEN totalAppSize*(1.0*T.dataCompressed/totalBackupSize) ELSE T.dataCompressed END
	FROM	(SELECT J.jobId, J.commCellId, J.archGrpCopyId, SUM(AFC.physicalSize) AS dataCompressed
			FROM	#PartialJobSize J
					INNER JOIN archFile AF WITH (NOLOCK)
					ON AF.jobId = J.jobId AND AF.commCellId = J.commCellId
					INNER JOIN archFileCopy AFC WITH (NOLOCK) 
					ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId AND AFC.archCopyId = J.archGrpCopyId
			GROUP BY J.jobId, J.commCellId, J.archGrpCopyId
			) T
	WHERE	#PartialJobSize.jobId = T.jobId AND #PartialJobSize.commCellId = T.commCellId AND #PartialJobSize.archGrpCopyId = T.archGrpCopyId

	INSERT	INTO #CopyReadSize
	SELECT	archGrpCopyId, -SUM(totalAppSize - dataRead)
	FROM	#PartialJobSize
	GROUP BY archGrpCopyId

	DROP TABLE #PartialJobSize

	-- Include all volumes except physically deleted volumes
	-- Volumes of deleted copies using GDSP are under GDSP host copy
	;WITH CopyWriteSize (activeStoreId, dataWritten) AS
	(	SELECT	ActiveStoreId, SUM(SS.PhysicalBytesMB)*1024*1024
		FROM (	SELECT	AGC.SIDBStoreId AS ActiveStoreId, CS.SIDBStoreId
				FROM	archCopySIDBStore CS WITH (NOLOCK) 
						INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON CS.CopyId = AGC.id
				WHERE	((AGC.dedupeFlags & 262144) > 0)
				GROUP BY AGC.SIDBStoreId, CS.SIDBStoreId
			) SN
			INNER JOIN (
				SELECT	SIDBStoreId, SUM(CAST(V.PhysicalBytesMB AS BIGINT)) AS PhysicalBytesMB
				FROM	MMVolume V WITH (NOLOCK)
						INNER JOIN #CommCell CC ON V.origCCcommCellId = CC.commCellId
				WHERE	SIDBStoreId > 0 AND SiloStatus <> 3 AND (Attributes & 512) = 0
				GROUP BY SIDBStoreId
			) SS ON SN.SIDBStoreId = SS.SIDBStoreId
		GROUP BY ActiveStoreId
	)
	UPDATE #DedupeCopy
	SET		dataRead = R.dataRead, dataWritten = W.dataWritten, dedupRatio = 1.0*W.dataWritten/R.dataRead
	FROM	(SELECT	DC.activeStoreId, SUM(RS.dataRead) AS dataRead
			FROM	#CopyReadSize RS
					INNER JOIN #DedupeCopy DC ON RS.archGrpCopyId = DC.archGrpCopyId
			GROUP BY DC.activeStoreId) R
			INNER JOIN CopyWriteSize W ON W.activeStoreId = R.activeStoreId
			INNER JOIN #DedupeCopy C ON C.activeStoreId = R.activeStoreId
	WHERE	R.dataRead > 0

	DROP TABLE #CopyReadSize
END

CREATE TABLE #CopyDataRetention (copyId INT, AgeIn30DaysMB BIGINT, AgeIn30_90DaysMB BIGINT, AgeIn90_365DaysMB BIGINT, AgeAfter365DaysMB BIGINT)

INSERT INTO #CopyDataRetention
SELECT	copyId, 0, 0, 0, 0
FROM	archAgingRule WITH (NOLOCK)

-- 1. Copies that data will not age

; WITH NonDedupCopy (copyId) AS 
(
	SELECT	copyId
	FROM	archAgingRule AR WITH (NOLOCK)
			LEFT OUTER JOIN #DedupeCopy DC ON AR.copyId = DC.archGrpCopyId
	WHERE	DC.archGrpCopyId IS NULL 
		AND (AR.retentionDays = -1 AND AR.archiverRetDays = -1 OR (AR.flags & (4|128)) > 0)
)
UPDATE	T SET AgeAfter365DaysMB += sizeOnMedia/1024/1024
FROM	#CopyDataRetention T INNER JOIN (
	SELECT	C.copyId, SUM(sizeOnMedia) AS sizeOnMedia
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN NonDedupCopy C ON J.archGrpCopyId = C.copyId 
			INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
	WHERE	(J.retentionFlags & 128) = 0 AND J.status = 100 AND (J.disabled & 256) = 0
	GROUP BY C.copyId
	) S ON T.copyId = S.copyId

UPDATE	T SET AgeAfter365DaysMB += sizeOnMedia/1024/1024
FROM	#CopyDataRetention T INNER JOIN (
	SELECT	AR.copyId, DC.dataWritten AS sizeOnMedia
	FROM	archAgingRule AR WITH (NOLOCK)
			INNER JOIN #DedupeCopy DC ON AR.copyId = DC.archGrpCopyId
	WHERE	(AR.retentionDays = -1 AND AR.archiverRetDays = -1 OR (AR.flags & (4|128)) > 0)
	) S ON T.copyId = S.copyId


-- 2. Copies that data will age in 30 days

; WITH ShortLifeCopy (copyId) AS 
(
	SELECT	copyId
	FROM	archAgingRule AR WITH (NOLOCK)
			LEFT OUTER JOIN #DedupeCopy DC ON AR.copyId = DC.archGrpCopyId
	WHERE	DC.archGrpCopyId IS NULL 
		AND AR.retentionDays > -1 AND AR.retentionDays <= 30 
		AND AR.archiverRetDays > -1 AND AR.archiverRetDays <= 30
		AND (AR.flags & 4) = 0
)
UPDATE	T SET AgeIn30DaysMB += sizeOnMedia/1024/1024
FROM	#CopyDataRetention T INNER JOIN (
	SELECT	C.copyId, SUM(J.sizeOnMedia) AS sizeOnMedia
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN ShortLifeCopy C ON J.archGrpCopyId = C.copyId 
			INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
	WHERE	(J.retentionFlags & 128) = 0 AND J.status = 100 AND (J.disabled & 256) = 0
	GROUP BY C.copyId
	) S ON T.copyId = S.copyId

UPDATE	T SET AgeIn30DaysMB += sizeOnMedia/1024/1024
FROM	#CopyDataRetention T INNER JOIN (
	SELECT	AR.copyId, DC.dataWritten AS sizeOnMedia
	FROM	archAgingRule AR WITH (NOLOCK)
			INNER JOIN #DedupeCopy DC ON AR.copyId = DC.archGrpCopyId
	WHERE	AR.retentionDays > -1 AND AR.retentionDays <= 30 
		AND AR.archiverRetDays > -1 AND AR.archiverRetDays <= 30
		AND (AR.flags & 4) = 0
	) S ON T.copyId = S.copyId


-- 3. Jobs without extended or manual retention

CREATE TABLE #CopyRetention (copyId INT, retentionDays INT, archiverRetDays INT, flags INT, extRetFlags INT, dedupCopy INT, dedupRatio FLOAT)

INSERT	INTO #CopyRetention
SELECT	AR.copyId, CASE WHEN AR.retentionDays = -1 THEN 0x7FFFFFFF ELSE AR.retentionDays END, 
		CASE WHEN AR.archiverRetDays = -1 THEN 0x7FFFFFFF ELSE AR.archiverRetDays END, AR.flags, 
		ISNULL(E.extRetFlags, 0), ISNULL(DC.archGrpCopyId, 0), ISNULL(DC.dedupRatio, 1.0)
FROM	archAgingRule AR WITH (NOLOCK)
		LEFT OUTER JOIN (SELECT copyId, SUM(retentionRule) AS extRetFlags FROM archAgingRuleExtended WITH (NOLOCK) GROUP BY copyId) E ON AR.copyId = E.copyId
		LEFT OUTER JOIN #DedupeCopy DC ON AR.copyId = DC.archGrpCopyId
WHERE	(AR.flags & (4|128)) = 0 
	AND (AR.retentionDays > -1 OR AR.archiverRetDays > -1) 
	AND (AR.retentionDays = -1 OR AR.retentionDays > 30 OR AR.archiverRetDays = -1 OR AR.archiverRetDays > 30)

-- 3a. Jobs are not deduped

INSERT	INTO #FiveDaysSize
SELECT	C.copyId, ((CASE WHEN ISNULL(H.isDMAppType, 0) = 1 THEN C.archiverRetDays ELSE C.retentionDays END)- (@today - B.servStartDate)/86400)/5,
		SUM(J.sizeOnMedia) AS DataWritten
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId AND C.dedupCopy = 0
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId
		LEFT OUTER JOIN APP_IDATypeHandlingInfo H WITH (NOLOCK) ON B.appType = H.appTypeId
WHERE	(J.retentionFlags & 128) = 0 AND (C.extRetFlags = 0 OR J.retentionFlags = 0) 
	AND J.status = 100 AND (J.disabled & 256) = 0
GROUP BY C.copyId, ((CASE WHEN ISNULL(H.isDMAppType, 0) = 1 THEN C.archiverRetDays ELSE C.retentionDays END)- (@today - B.servStartDate)/86400)/5

INSERT	INTO #FiveDaysSize
SELECT	C.copyId, (C.retentionDays - (@today - A.servStart)/86400)/5,
		SUM(J.sizeOnMedia) AS DataWritten
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId AND C.dedupCopy = 0
		INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId
WHERE	(J.retentionFlags & 128) = 0 AND (C.extRetFlags = 0 OR J.retentionFlags = 0) 
	AND J.status = 100 AND (J.disabled & 256) = 0
GROUP BY C.copyId, (C.retentionDays - (@today - A.servStart)/86400)/5


-- 3b. Jobs are deduped

INSERT	INTO #FiveDaysSize
SELECT	C.copyId, ((CASE WHEN ISNULL(H.isDMAppType, 0) = 1 THEN C.archiverRetDays ELSE C.retentionDays END)- (@today - B.servStartDate)/86400)/5,
		SUM(B.totalUncompBytes*C.dedupRatio) AS DataWritten
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId AND C.dedupCopy > 0
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 1
		LEFT OUTER JOIN APP_IDATypeHandlingInfo H ON B.appType = H.appTypeId
WHERE	J.archGrpId = B.dataArchGrpId AND J.status = 100 AND (J.disabled & 256) = 0
	AND	(J.retentionFlags & 128) = 0 AND (C.extRetFlags = 0 OR J.retentionFlags = 0)
GROUP BY C.copyId, ((CASE WHEN ISNULL(H.isDMAppType, 0) = 1 THEN C.archiverRetDays ELSE C.retentionDays END)- (@today - B.servStartDate)/86400)/5

INSERT	INTO #FiveDaysSize
SELECT	C.copyId, ((CASE WHEN ISNULL(H.isDMAppType, 0) = 1 THEN C.archiverRetDays ELSE C.retentionDays END)- (@today - B.servStartDate)/86400)/5,
		SUM(B.totalUncompBytes*C.dedupRatio) AS DataWritten
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId AND C.dedupCopy > 0
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 4
		LEFT OUTER JOIN APP_IDATypeHandlingInfo H ON B.appType = H.appTypeId
WHERE	J.archGrpId = B.logArchGrpId AND B.dataBackedUp = 0 AND J.status = 100 AND (J.disabled & 256) = 0
	AND	(J.retentionFlags & 128) = 0 AND (C.extRetFlags = 0 OR J.retentionFlags = 0)
GROUP BY C.copyId, ((CASE WHEN ISNULL(H.isDMAppType, 0) = 1 THEN C.archiverRetDays ELSE C.retentionDays END)- (@today - B.servStartDate)/86400)/5

INSERT	INTO #FiveDaysSize
SELECT	C.copyId, (C.retentionDays - (@today - A.servStart)/86400)/5,
		SUM(A.totalBackupSize*C.dedupRatio) AS DataWritten
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId AND C.dedupCopy > 0
		INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId AND J.dataType = 1
WHERE	J.status = 100 AND (J.disabled & 256) = 0
	AND	(J.retentionFlags & 128) = 0 AND (C.extRetFlags = 0 OR J.retentionFlags = 0)
GROUP BY C.copyId, (C.retentionDays - (@today - A.servStart)/86400)/5


-- 4. Extended retained jobs

CREATE TABLE #RetainedJobs (jobId INT, commCellId INT, archGrpCopyId INT, colBigInt BIGINT)

INSERT	INTO #RetainedJobs
SELECT	J.jobId, J.commCellId, J.archGrpCopyId, MAX(J.retentionFlags & C.retentionRule)
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN archAgingRuleExtended C WITH (NOLOCK) ON J.archGrpCopyId = C.copyId
WHERE	J.status = 100 AND (J.disabled & 256) = 0
	AND	(J.retentionFlags & 128) = 0 AND (J.retentionFlags & C.retentionRule) > 0
GROUP BY J.jobId, J.commCellId, J.archGrpCopyId

; WITH FiveDaysSize (copyId, nFiveDays, DataWritten) AS
(
	SELECT	C.copyId, (E.retentionDays - (@today - B.servStartDate)/86400)/5 AS nFiveDays,
			SUM(CASE WHEN C.dedupCopy > 0 THEN B.totalUncompBytes*C.dedupRatio ELSE B.totalBackupSize END) AS DataWritten
	FROM	#RetainedJobs J
			INNER JOIN archAgingRuleExtended E WITH (NOLOCK) ON J.archGrpCopyId = E.copyId AND J.colBigInt = E.retentionRule
			INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId
			LEFT OUTER JOIN APP_IDATypeHandlingInfo H WITH (NOLOCK) ON B.appType = H.appTypeId
	WHERE	ISNULL(H.isDMAppType, 0) = 0
	GROUP BY C.copyId, (E.retentionDays - (@today - B.servStartDate)/86400)/5
	UNION ALL
	SELECT	C.copyId, (E.retentionDays - (@today - A.servStart)/86400)/5 AS nFiveDays,
			SUM(CASE WHEN C.dedupCopy > 0 THEN A.totalBackupSize*C.dedupRatio ELSE A.totalBackupSize END) AS DataWritten
	FROM	#RetainedJobs J
			INNER JOIN archAgingRuleExtended E WITH (NOLOCK) ON J.archGrpCopyId = E.copyId AND J.colBigInt = E.retentionRule
			INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId
			INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId
	GROUP BY C.copyId, (E.retentionDays - (@today - A.servStart)/86400)/5
)
INSERT	INTO #FiveDaysSize
SELECT	copyId, nFiveDays, DataWritten
FROM	FiveDaysSize

DELETE FROM #RetainedJobs

-- 5. Manually retained jobs

INSERT	INTO #RetainedJobs
SELECT	J.jobId, J.commCellId, J.archGrpCopyId, MAX(manualRetentionEndTime)
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #CommCell CC ON J.commCellId = CC.commCellId
		INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId
WHERE	J.status = 100 AND (J.disabled & 256) = 0
	AND	(J.retentionFlags & 128) > 0 AND (C.flags & (4|128)) = 0 
GROUP BY J.jobId, J.commCellId, J.archGrpCopyId


; WITH FiveDaysSize (copyId, nFiveDays, DataWritten) AS
(
	SELECT	C.copyId, (@today - colBigInt)/432000 AS nFiveDays,
			SUM(CASE WHEN C.dedupCopy > 0 THEN B.totalUncompBytes*C.dedupRatio ELSE B.totalBackupSize END) AS DataWritten
	FROM	#RetainedJobs J
			INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId
	GROUP BY C.copyId, (@today - colBigInt)/432000
	UNION ALL
	SELECT	C.copyId, (@today - colBigInt)/432000 AS nFiveDays,
			SUM(CASE WHEN C.dedupCopy > 0 THEN A.totalBackupSize*C.dedupRatio ELSE A.totalBackupSize END) AS DataWritten
	FROM	#RetainedJobs J
			INNER JOIN #CopyRetention C ON J.archGrpCopyId = C.copyId
			INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId
	GROUP BY C.copyId, (@today - colBigInt)/432000
)
INSERT	INTO #FiveDaysSize
SELECT	copyId, nFiveDays, DataWritten
FROM	FiveDaysSize


; WITH BucketSize (copyId, Bucket, DataWrittenMB) AS
(
	SELECT	copyId, CASE WHEN nFiveDays < 6 THEN 1 WHEN nFiveDays < 18 THEN 2 WHEN nFiveDays < 72 THEN 3 ELSE 4 END AS Bucket,
			SUM(DataWritten)/1024/1024 AS DataWrittenMB
	FROM	#FiveDaysSize
	GROUP BY copyId, CASE WHEN nFiveDays < 6 THEN 1 WHEN nFiveDays < 18 THEN 2 WHEN nFiveDays < 72 THEN 3 ELSE 4 END
)
UPDATE	T SET 
		AgeIn30DaysMB     += ISNULL(B.P1, 0), 
		AgeIn30_90DaysMB  += ISNULL(B.P2, 0),
		AgeIn90_365DaysMB += ISNULL(B.P3, 0),
		AgeAfter365DaysMB += ISNULL(B.P4, 0)
FROM	#CopyDataRetention T 
		INNER JOIN (
		SELECT	copyId, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4
		FROM	BucketSize PIVOT (MAX(DataWrittenMB) FOR Bucket IN ([1], [2], [3], [4])) AS P
		) B ON T.copyId = B.copyId

DELETE	#CopyDataRetention WHERE (AgeIn30DaysMB + AgeIn30_90DaysMB + AgeIn90_365DaysMB + AgeAfter365DaysMB) = 0

INSERT	INTO #CopyDataRetention
SELECT	0, SUM(AgeIn30DaysMB), SUM(AgeIn30_90DaysMB), SUM(AgeIn90_365DaysMB), SUM(AgeAfter365DaysMB)
FROM	#CopyDataRetention

-- Return the results
SET @surveyXML = 
(
	SELECT	
		copyId            AS '@CopyId', 
		AgeIn30DaysMB     AS '@AgeIn30DaysMB', 
		AgeIn30_90DaysMB  AS '@AgeIn30To90DaysMB',
		AgeIn90_365DaysMB AS '@AgeIn90To365DaysMB', 
		AgeAfter365DaysMB AS '@AgeAfter365DaysMB'
	FROM	#CopyDataRetention
	ORDER BY copyId
		
	FOR XML PATH('StorageDataRetention')
)

DROP TABLE #CommCell
DROP TABLE #DedupeCopy
DROP TABLE #FiveDaysSize
DROP TABLE #RetainedJobs
DROP TABLE #CopyRetention
DROP TABLE #CopyDataRetention

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