--- 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 = 55
DECLARE @surveyXML NVARCHAR(MAX)

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



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

--------- Insert your SQL statements here 
-- Query Name: Average Backup Throughput in last 8 days
-- Description: Calculate bkp jobs throughput to tape and disk for last 8 days.
--		Throughput is calculated as the ratio of Application Size to Write time


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

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

 

DECLARE @DayTbl TABLE (DateVal DATETIME, DayBeginDT DATETIME, DayEndDT DATETIME, DayBegin INT, DayEnd INT)

DECLARE	@j			INT = 1
DECLARE @dtBegin	DATETIME = DATEADD(DD, DATEDIFF(DD, 1, GETDATE()), 0)
DECLARE @dtEnd		DATETIME = DATEADD(DD, 1, @dtBegin)

DECLARE @fromDt DATETIME
DECLARE @toDt DATETIME

WHILE @j <= 8
BEGIN

	SET @fromDt = @dtBegin
	SET @toDt = @dtEnd
	
	IF OBJECT_ID('dbo.IsInvalidTimeManaged') IS NOT NULL
	BEGIN
		WHILE dbo.IsInvalidTimeManaged(@fromDt, @csTimeZone) = 1
		BEGIN
			SET @fromDt = DATEADD(HOUR, 1, @fromDt)
		END
		
		WHILE dbo.IsInvalidTimeManaged(@toDt, @csTimeZone) = 1
		BEGIN
			SET @toDt = DATEADD(HOUR, 1, @toDt)
		END
	END
	
	INSERT	INTO @DayTbl
	SELECT	@dtBegin, dbo.LocalToUTCTime(@fromDt, @csTimeZone), dbo.LocalToUTCTime(@toDt, @csTimeZone), 0, 0
		
	SET @dtBegin = DATEADD(DAY, DATEDIFF(DAY, 0, @dtBegin), 0)
	SET @dtEnd	 = @dtBegin
	SET @dtBegin = DATEADD(DAY, -1, @dtEnd)
	SET @j = (@j + 1)
END

UPDATE	@DayTbl SET DayBegin = dbo.GetUnixTime(DayBeginDT), DayEnd = dbo.GetUnixTime(DayEndDT)

DECLARE @fromUnixTime INT = (SELECT MIN(DayBegin) FROM @DayTbl)
DECLARE @toUnixTime   INT = (SELECT MAX(DayEnd) FROM @DayTbl)

--0. Auxiliary Copy throughput per media type (disk, tape, cloud)

CREATE TABLE #AuxCopyDailyMediaUsage (WriteTime INT, AppSize BIGINT, DateVal DateTime, MediaTypes INT)

DECLARE @AuxCopyMediaUsage INT = 0

IF COL_LENGTH('archChunkMapping', 'unCompSize') IS NOT NULL
BEGIN
	SET @AuxCopyMediaUsage = 1
	
	CREATE TABLE #AuxCopyJob (AuxCopyJobId INT, StartTime INT, EndTime INT, WriteTime INT, ArchGrpId INT, CopyId INT, DateVal DateTime)
	CREATE TABLE #AuxCopyJobAppSize (AuxCopyJobId INT, WriteTime INT, DateVal DATETIME, MediaTypes INT, AppSize BIGINT)

	INSERT  INTO #AuxCopyJob
	SELECT	jobId, servStart, servEnd, totalWriteTime, archGrpID, archGrpCopyID, NULL
	FROM	JMAdminJobStatsTable WITH (NOLOCK)
	WHERE	opType IN (13, 104) AND status = 1 AND servEnd > @fromUnixTime AND totalWriteTime > 0

	INSERT  INTO #AuxCopyJob
	SELECT	jobId, servStart, @toUnixTime-1, currentWriteTime, archGrpID, archGrpCopyID, NULL
	FROM	JMAdminJobInfoTable WITH (NOLOCK)
	WHERE	opType IN (13, 104) AND servEnd > @fromUnixTime AND currentWriteTime > 0
	
	UPDATE	AJ SET DateVal = W.DateVal
	FROM	#AuxCopyJob AJ
			INNER JOIN @DayTbl W ON AJ.EndTime >= W.DayBegin AND AJ.EndTime < W.DayEnd
			
	INSERT  INTO #AuxCopyJob
	SELECT	AJ.AuxCopyJobId, AJ.StartTime, AJ.EndTime, AJ.WriteTime, AJ.ArchGrpId, AGC.id, AJ.DateVal
	FROM	archGroup AG WITH (NOLOCK) 
			INNER JOIN archGroupCopy AGC WITH (NOLOCK) 
				ON AGC.archGroupId = AG.id AND AGC.id <> AG.defaultCopy AND AGC.type IN (1,2) AND AGC.isSnapCopy = 0
			INNER JOIN #AuxCopyJob AJ ON AJ.ArchGrpId = AGC.archGroupId AND AJ.CopyId <= 0

	CREATE CLUSTERED INDEX #AuxCopyJob_CopyId_StartTime_EndTime_idx ON #AuxCopyJob (CopyId, StartTime, EndTime)

	DELETE FROM #AuxCopyJob WHERE CopyId <= 0

	SET @surveyXML = N'
		DECLARE @fromUnixTime INT = (SELECT MIN(startTime) FROM #AuxCopyJob)

		INSERT	INTO #AuxCopyJobAppSize
		SELECT	
			AJ.AuxCopyJobId, AJ.WriteTime, AJ.DateVal,
			CASE V.RecordingFormatId 
				WHEN 10001 THEN (CASE ISNULL(MPT.MountPathTypeId, -1) WHEN -1 THEN -1 WHEN 7 THEN 4 ELSE 2 END)
				ELSE 1 END,
			SUM(unCompSize)
		FROM  
			#AuxCopyJob AJ
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) 
				ON ACM.archCopyId = AJ.CopyId
			INNER JOIN archChunk AC WITH (NOLOCK) 
				ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId 
					AND AC.createTime >= AJ.StartTime AND AC.createTime < AJ.EndTime
			INNER JOIN MMVolume V WITH (NOLOCK) 
				ON V.VolumeId = AC.volumeId
			LEFT OUTER JOIN MMMountPath MPT WITH (NOLOCK) 
				ON MPT.MediaSideId = V.MediaSideId
		WHERE
			AC.createTime >= @fromUnixTime
			AND ACM.physicalSize > 0
		GROUP BY AJ.AuxCopyJobId, AJ.WriteTime, AJ.DateVal,
			CASE V.RecordingFormatId 
				WHEN 10001 THEN (CASE ISNULL(MPT.MountPathTypeId, -1) WHEN -1 THEN -1 WHEN 7 THEN 4 ELSE 2 END)
				ELSE 1 END'
	
	EXEC sp_executesql @surveyXML

	INSERT	INTO  #AuxCopyDailyMediaUsage
	SELECT	SUM(WriteTime), SUM(AppSize), DateVal, MediaTypes
	FROM	#AuxCopyJobAppSize
	GROUP BY DateVal, MediaTypes
	
	DROP TABLE #AuxCopyJob
	DROP TABLE #AuxCopyJobAppSize
END


--1. Backup throughput per media type (disk, tape, cloud)
CREATE TABLE #DailyMediaUsage (WriteTime BIGINT, AppSize BIGINT, DateVal DateTime, MediaTypes INT)

CREATE TABLE #CopyMediaType (archGrpId INT, copyId INT, mediaTypes INT)

CREATE TABLE #MixedCopyJobs (jobId INT, endTime INT, appSize BIGINT, writeTime INT, 
		copyId INT, afSize BIGINT, afSizeOnDisk BIGINT, afSizeOnCloud BIGINT, mediaTypes INT)

CREATE TABLE #PrimaryCopy (copyId INT)

INSERT	INTO #PrimaryCopy
SELECT
	AG.defaultCopy
FROM  
	JMBkpStats J WITH (NOLOCK)
	INNER JOIN archGroup AG WITH (NOLOCK) 
		ON (J.dataArchGrpId > 1 AND AG.id = J.dataArchGrpId OR J.dataBackedUp = 0 AND AG.id = J.logArchGrpId)
WHERE
	J.opType IN (4, 18, 43) AND J.commCellId = 2
	AND J.servEndDate >= @fromUnixTime AND J.status IN (1, 3, 14)
GROUP BY
	AG.defaultCopy

INSERT	INTO #CopyMediaType
SELECT 
	S.archGroupId, S.archGroupCopyId, 
	CASE V.RecordingFormatId WHEN 10001 THEN (CASE ISNULL(MPT.MountPathTypeId, -1) WHEN -1 THEN -1 WHEN 7 THEN 4 ELSE 2 END) ELSE 1 END
FROM
	#PrimaryCopy C
	INNER JOIN archStream S WITH (NOLOCK) ON C.copyId = S.archGroupCopyId
	INNER JOIN MMVolume V WITH (NOLOCK) 
		ON V.MediaGroupId = S.mediaGroupId AND V.PhysicalBytesMB > 0
	LEFT OUTER JOIN MMMountPath MPT WITH (NOLOCK) 
		ON MPT.MediaSideId = V.MediaSideId
GROUP BY S.archGroupId, S.archGroupCopyId, 
	CASE V.RecordingFormatId WHEN 10001 THEN (CASE ISNULL(MPT.MountPathTypeId, -1) WHEN -1 THEN -1 WHEN 7 THEN 4 ELSE 2 END) ELSE 1 END

DROP TABLE #PrimaryCopy


-- If archFileCopy entries of jobs in last 8 days have been deleted, set isDisk using the default data path
IF EXISTS (SELECT copyId FROM #CopyMediaType WHERE mediaTypes = -1)
	UPDATE	#CopyMediaType SET mediaTypes = (isTape + isDisk + isCloud)
	FROM	(SELECT	DPT.CopyId, 
			MAX(CASE WHEN DrivePoolType = 10001 THEN 0 ELSE 1 END) AS isTape,
			MAX(CASE WHEN DrivePoolType = 10001 THEN 2 ELSE 0 END) AS isDisk,
			MAX(CASE WHEN ISNULL(MPT.MountPathTypeId, -1) = 7 THEN 4 ELSE 0 END) AS isCloud
		FROM	#CopyMediaType C
			INNER JOIN MMDataPath DPT WITH (NOLOCK) ON C.copyId = DPT.CopyId 
			INNER JOIN MMDrivePool DPL WITH (NOLOCK) ON DPT.DrivePoolId = DPL.DrivePoolId AND (DPT.Flag & 1) = 1
			LEFT OUTER JOIN MMMountPath MPT WITH (NOLOCK) ON DPL.MasterPoolId = MPT.MasterPoolId
		WHERE	C.mediaTypes = -1
		GROUP BY DPT.CopyId
		) S
	WHERE	#CopyMediaType.copyId = S.CopyId

IF EXISTS (SELECT * FROM #CopyMediaType WHERE mediaTypes = 3)
BEGIN
	-- Jobs on both disk and non-disk media
	INSERT INTO #MixedCopyJobs
	SELECT	J.jobId, J.servEndDate, J.totalUncompBytes, J.totalWriteTime, C.copyId, 
			SUM(AFC.physicalSize), SUM(CASE WHEN AFC.mediaType = 10001 THEN AFC.physicalSize ELSE 0 END), 0, 
			MAX(CASE WHEN AFC.mediaType = 10001 THEN 2 ELSE 1 END)
	FROM 
		JMBkpStats J WITH (NOLOCK) 
		INNER JOIN #CopyMediaType C
			ON (J.dataArchGrpId > 1 AND C.archGrpId = J.dataArchGrpId OR J.dataBackedUp = 0 AND C.archGrpId = J.logArchGrpId)
		INNER JOIN archFile AF WITH (NOLOCK) 
			ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId 
		INNER JOIN archFileCopy AFC WITH (NOLOCK) 
			ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId AND AFC.archCopyId = C.copyId
	WHERE	
		J.opType IN (4, 18, 43) AND J.commCellId = 2
		AND J.servEndDate >= @fromUnixTime AND J.status IN (1, 3, 14) AND C.mediaTypes = 3
		AND AF.isValid = 1 AND AFC.isValid = 1 AND AFC.physicalSize > 0 AND AFC.mediaType > 0
 		AND J.totalWriteTime <= (J.servEndDate - J.servStartDate)
	GROUP BY J.jobId, J.servEndDate, J.totalUncompBytes, J.totalWriteTime, C.copyId
END

IF EXISTS (SELECT * FROM #CopyMediaType WHERE mediaTypes IN (5,6))
BEGIN
	-- Jobs on both cloud and non-cloud media
	INSERT INTO #MixedCopyJobs
	SELECT	J.jobId, J.servEndDate, J.totalUncompBytes, J.totalWriteTime, C.copyId, 
			SUM(AFC.physicalSize), SUM(CASE WHEN AFC.mediaType = 10001 THEN AFC.physicalSize ELSE 0 END), 0,
			MAX(CASE WHEN AFC.mediaType = 10001 THEN (CASE WHEN ISNULL(MPT.MountPathTypeId, -1) = 7 THEN 4 ELSE 2 END) ELSE 1 END)
	FROM 
		JMBkpStats J WITH (NOLOCK) 
		INNER JOIN #CopyMediaType C
			ON (J.dataArchGrpId > 1 AND C.archGrpId = J.dataArchGrpId OR J.dataBackedUp = 0 AND C.archGrpId = J.logArchGrpId)
		INNER JOIN archFile AF WITH (NOLOCK) 
			ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId 
		INNER JOIN archFileCopy AFC WITH (NOLOCK) 
			ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId AND AFC.archCopyId = C.copyId
		INNER JOIN archChunkMapping ACM WITH (NOLOCK) 
			ON ACM.archFileId = AF.id AND ACM.commCellId = AF.commCellId AND ACM.archCopyId = C.copyId
		INNER JOIN archChunk AC WITH (NOLOCK) 
			ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
		INNER JOIN MMVolume V WITH (NOLOCK) 
			ON V.VolumeId = AC.volumeId
		LEFT OUTER JOIN MMMountPath MPT WITH (NOLOCK) 
			ON MPT.MediaSideId = V.MediaSideId
	WHERE	
		J.opType IN (4, 18, 43) AND J.commCellId = 2
		AND J.servEndDate >= @fromUnixTime AND J.status IN (1, 3, 14) AND C.mediaTypes IN (5,6)
		AND AF.isValid = 1 AND AFC.isValid = 1 AND AFC.physicalSize > 0 AND AFC.mediaType > 0
 		AND J.totalWriteTime <= (J.servEndDate - J.servStartDate)
	GROUP BY J.jobId, J.servEndDate, J.totalUncompBytes, J.totalWriteTime, C.copyId
END


-- #CopyMediaType.mediaTypes = 1, 2 or 4
INSERT INTO #DailyMediaUsage
SELECT
	SUM(CAST(J.totalWriteTime AS BIGINT)), 
	SUM(J.totalUncompBytes),
	W.DateVal,
	C.mediaTypes
FROM 
	JMBkpStats J WITH (NOLOCK) 
	INNER JOIN #CopyMediaType C
		ON (J.dataArchGrpId > 1 AND C.archGrpId = J.dataArchGrpId OR J.dataBackedUp = 0 AND C.archGrpId = J.logArchGrpId)
	INNER JOIN @DayTbl AS W
		ON J.servEndDate >= W.DayBegin AND J.servEndDate < W.DayEnd
WHERE	
	J.servEndDate >= @fromUnixTime 
	AND J.opType IN (4, 18, 43) AND J.commCellId = 2
	AND J.status IN (1, 3, 14) AND C.mediaTypes IN (1,2,4)
	AND J.totalWriteTime <= (J.servEndDate - J.servStartDate)
GROUP BY W.DateVal, C.mediaTypes

-- Jobs on single type of media
IF EXISTS (SELECT * FROM #MixedCopyJobs WHERE mediaTypes IN (1,2,4))
BEGIN
	UPDATE	#DailyMediaUsage
	SET		appSize += T.appSize, writeTime += T.writeTime
	FROM
		(SELECT
			SUM(appSize) AS appSize, 
			SUM(writeTime) AS writeTime,
			mediaTypes,
			W.DateVal
		FROM 
			#MixedCopyJobs J
			INNER JOIN @DayTbl AS W ON J.endTime >= W.DayBegin AND J.endTime < W.DayEnd
				AND mediaTypes IN (1,2,4)
		GROUP BY mediaTypes, W.DateVal) T
	WHERE	#DailyMediaUsage.DateVal = T.DateVal AND #DailyMediaUsage.mediaTypes = T.mediaTypes
END

-- Jobs on multiple types of media
IF EXISTS (SELECT * FROM #MixedCopyJobs WHERE mediaTypes NOT IN (1,2,4))
BEGIN
	UPDATE	#DailyMediaUsage
	SET		appSize += T.appSize, writeTime += T.writeTime
	FROM
		(SELECT
			SUM((1.0*appSize/afSize)*(CASE mediaTypes 
				WHEN 1 THEN (afSize-afSizeOnDisk-afSizeOnCloud) 
				WHEN 2 THEN afSizeOnDisk ELSE afSizeOnCloud END)
			) AS appSize, 
			SUM((1.0*writeTime/afSize)*(CASE mediaTypes 
				WHEN 1 THEN (afSize-afSizeOnDisk-afSizeOnCloud) 
				WHEN 2 THEN afSizeOnDisk ELSE afSizeOnCloud END)
			) AS writeTime,
			mediaTypes,
			W.DateVal
		FROM 
			#MixedCopyJobs J
			INNER JOIN @DayTbl AS W ON J.endTime >= W.DayBegin AND J.endTime < W.DayEnd
				AND J.mediaTypes NOT IN (1,2,4)
		GROUP BY mediaTypes, W.DateVal) T
	WHERE	#DailyMediaUsage.DateVal = T.DateVal AND #DailyMediaUsage.MediaTypes = T.mediaTypes
END

DROP TABLE #CopyMediaType
DROP TABLE #MixedCopyJobs


--2. Backup throughput per agent type
CREATE TABLE #DailyBkpStatsByAgent (DateVal DateTime, AppTypeId INT, WriteTime BIGINT, AppSizeGB REAL)

INSERT INTO #DailyBkpStatsByAgent
SELECT
	W.DateVal,
	J.appType,
	SUM(CAST(J.totalWriteTime AS BIGINT)), 
	SUM(CAST(J.totalUncompBytes AS REAL))/1024/1024/1024
FROM 
	JMBkpStats J WITH (NOLOCK) 
	INNER JOIN @DayTbl AS W ON J.servEndDate >= W.DayBegin AND J.servEndDate < W.DayEnd
WHERE	
	J.servEndDate >= @fromUnixTime 
	AND J.opType IN (4, 18, 43) AND J.commCellId = 2 AND J.status IN (1, 3, 14) 
	AND J.totalUncompBytes > 0 AND J.totalWriteTime <= (J.servEndDate - J.servStartDate)
GROUP BY W.DateVal, J.appType

--3. Backup throughput per storage policy
CREATE TABLE #DailyBkpStatsByPolicy (DateVal DateTime, ArchGroupId INT, WriteTime BIGINT, AppSizeGB REAL)

INSERT INTO #DailyBkpStatsByPolicy
SELECT
	W.DateVal,
	CASE WHEN J.dataArchGrpId > 1 THEN J.dataArchGrpId ELSE J.logArchGrpId END,
	SUM(CAST(J.totalWriteTime AS BIGINT)), 
	SUM(CAST(J.totalUncompBytes AS REAL))/1024/1024/1024
FROM 
	JMBkpStats J WITH (NOLOCK) 
	INNER JOIN @DayTbl AS W ON J.servEndDate >= W.DayBegin AND J.servEndDate < W.DayEnd
WHERE	
	J.servEndDate >= @fromUnixTime 
	AND J.opType IN (4, 18, 43) AND J.commCellId = 2 AND J.status IN (1, 3, 14) 
	AND J.totalUncompBytes > 0 AND J.totalWriteTime <= (J.servEndDate - J.servStartDate)
GROUP BY W.DateVal, CASE WHEN J.dataArchGrpId > 1 THEN J.dataArchGrpId ELSE J.logArchGrpId END

--4. Restore throughput per agent type
CREATE TABLE #DailyRstStatsByAgent (DateVal DateTime, AppTypeId INT, WriteTime INT, AppSizeGB REAL)

INSERT INTO #DailyRstStatsByAgent
SELECT
	W.DateVal,
	R.appType,
	SUM(R.duration), 
	SUM(CAST(R.totUncompBytes_h AS REAL)*2147483648 + CAST(R.totUncompBytes_l AS REAL))/1024/1024/1024
FROM 
	JMJobStats J WITH (NOLOCK)
	INNER JOIN JMRestoreStats R WITH (NOLOCK) ON J.jobId = R.jobId AND J.commcellId = R.commcellId
	INNER JOIN @DayTbl AS W ON R.servEndTime >= W.DayBegin AND R.servEndTime < W.DayEnd
		AND (R.totUncompBytes_h > 0 OR R.totUncompBytes_l > 0)
WHERE	R.servEndTime >= @fromUnixTime AND R.status IN (1, 3, 14) 
	AND R.OpType IN (5, 40) AND J.subOpType <> 129 -- Exclude Restore Validate jobs
	AND (R.rstattributes&0x100/*JMRST_SYNC_RESTORE*/) = 0 -- Exclude completes sync restore jobs
GROUP BY W.DateVal, R.appType


DECLARE @AppTypeTbl TABLE (AppTypeId INT)

INSERT INTO @AppTypeTbl
SELECT	appType
FROM 	JMBkpStats WITH (NOLOCK)
WHERE	opType IN (4, 18, 43) AND commCellId = 2 AND status IN (1, 3, 14) AND totalUncompBytes > 0
GROUP BY appType

DECLARE @ArchGroupTbl TABLE (ArchGroupId INT, ArchGroupName NVARCHAR(144))

INSERT INTO @ArchGroupTbl
SELECT	CASE WHEN dataArchGrpId > 1 THEN dataArchGrpId ELSE logArchGrpId END, ''
FROM 	JMBkpStats WITH (NOLOCK)
WHERE	opType IN (4, 18, 43) AND commCellId = 2 AND status IN (1, 3, 14) AND totalUncompBytes > 0
GROUP BY CASE WHEN dataArchGrpId > 1 THEN dataArchGrpId ELSE logArchGrpId END

UPDATE 	T SET ArchGroupName = AG.name
FROM	@ArchGroupTbl T
	INNER JOIN archGroup AG ON T.ArchGroupId = AG.id

SET @surveyXML = (
	SELECT
	(SELECT 
		ISNULL(appSize, 0)/1024.0/1024.0/1024.0 AS '@AppSizeGB',
		ISNULL(writeTime, 0)/3600.0 AS '@WriteTimeHr',
		T.MediaTypes/2 AS '@IsDisk',
		T.DateVal AS '@EndDate'
	FROM
		(SELECT DateVal, 1 AS MediaTypes FROM @DayTbl UNION ALL 
		 SELECT DateVal, 2 FROM @DayTbl UNION ALL 
		 SELECT DateVal, 4 FROM @DayTbl) T
		LEFT OUTER JOIN #AuxCopyDailyMediaUsage D ON D.DateVal = T.DateVal AND D.MediaTypes = T.MediaTypes
	WHERE @AuxCopyMediaUsage = 1
	ORDER BY T.MediaTypes, T.DateVal
	FOR XML PATH('AuxCopyThroughput'), TYPE),
	
	(SELECT 
		ISNULL(appSize, 0)/1024.0/1024.0/1024.0 AS '@AppSizeGB',
		ISNULL(writeTime, 0)/3600.0 AS '@WriteTimeHr',
		T.MediaTypes/2 AS '@IsDisk',
		T.DateVal AS '@EndDate'
	FROM
		(SELECT DateVal, 1 AS MediaTypes FROM @DayTbl UNION ALL 
		 SELECT DateVal, 2 FROM @DayTbl UNION ALL 
		 SELECT DateVal, 4 FROM @DayTbl) T
		LEFT OUTER JOIN #DailyMediaUsage D ON D.DateVal = T.DateVal AND D.MediaTypes = T.MediaTypes
	ORDER BY T.MediaTypes, T.DateVal
	FOR XML PATH('AvgBkpThroughputInfo'), TYPE),
	
	(SELECT 
		A.AppTypeId AS '@AppTypeId',
		D.DateVal AS '@EndDate',
		ISNULL(AppSizeGB, 0) AS '@AppSizeGB',
		ISNULL(writeTime, 0) AS '@WriteTime'
	FROM
		@AppTypeTbl A CROSS JOIN @DayTbl D
		LEFT OUTER JOIN #DailyBkpStatsByAgent S ON S.AppTypeId = A.AppTypeId AND S.DateVal = D.DateVal
	ORDER BY A.AppTypeId, D.DateVal
	FOR XML PATH('BkpThroughputByAgent'), TYPE),
	
	(SELECT 
		A.ArchGroupId AS '@PolicyId',
		A.ArchGroupName AS '@PolicyName',
		D.DateVal AS '@EndDate',
		ISNULL(AppSizeGB, 0) AS '@AppSizeGB',
		ISNULL(writeTime, 0) AS '@WriteTime'
	FROM
		@ArchGroupTbl A CROSS JOIN @DayTbl D
		LEFT OUTER JOIN #DailyBkpStatsByPolicy S ON S.ArchGroupId = A.ArchGroupId AND S.DateVal = D.DateVal
	ORDER BY A.ArchGroupId, D.DateVal
	FOR XML PATH('BkpThroughputByPolicy'), TYPE),
	
	(SELECT 
		A.AppTypeId AS '@AppTypeId',
		D.DateVal AS '@EndDate',
		ISNULL(AppSizeGB, 0) AS '@AppSizeGB',
		ISNULL(writeTime, 0) AS '@WriteTime'
	FROM
		@AppTypeTbl A CROSS JOIN @DayTbl D
		LEFT OUTER JOIN #DailyRstStatsByAgent S ON S.AppTypeId = A.AppTypeId AND S.DateVal = D.DateVal
	ORDER BY A.AppTypeId, D.DateVal
	FOR XML PATH('RstThroughputByAgent'), TYPE)
	
	FOR XML PATH ('') 
)

DROP TABLE #DailyMediaUsage
DROP TABLE #DailyBkpStatsByAgent
DROP TABLE #DailyRstStatsByAgent
DROP TABLE #DailyBkpStatsByPolicy
DROP TABLE #AuxCopyDailyMediaUsage

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

SET NOCOUNT OFF

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