--@querytype    CSV

--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
BEGIN TRY

SET DATEFIRST 1

-- The data collection script 72, 136 and 162 should only differ in the followning line
DECLARE @queryId AS INTEGER = 136

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

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



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

--------- Insert your SQL statements here 
DECLARE @ErrorCode  INT = 0
CREATE TABLE #TblCopyIds (ArchGrpId INT, PolicyName NVARCHAR(144), CopyId INT, CopyName NVARCHAR(64), CopyType INT, 
		IsDefaultCopy INT, IsSnapCopy INT, dedupeFlags INT, StorageType VARCHAR(16));

CREATE TABLE #TblResults (PeriodBegin DATETIME, VMClientId INT, AppId INT, CopyId INT, 
		TotalDataProtected BIGINT, TotalDataOnMedia BIGINT, DataProtected BIGINT, DataWritten BIGINT, 
		FrontEndSize BIGINT, FrontEndSizeType INT, ApplicationSize BIGINT, BackupAppSize BIGINT)


--------------------------------------------
-- Set time range parameters              --
--------------------------------------------
DECLARE @CHARGEBACK_TYPE_MONTHLY INT = 1
DECLARE @CHARGEBACK_TYPE_WEEKLY INT = 2
DECLARE @CHARGEBACK_TYPE_DAILY INT = 4

DECLARE @Type INT = CASE @queryId WHEN 72 THEN @CHARGEBACK_TYPE_DAILY WHEN 162 THEN @CHARGEBACK_TYPE_WEEKLY WHEN 136 THEN @CHARGEBACK_TYPE_MONTHLY END
--------------------------------------------

DECLARE @isPrivateRunning VARCHAR(256) = ''
DECLARE @frequencyMode   NVARCHAR(MAX) ='0'

SELECT	@isPrivateRunning = value FROM GXGlobalParam WITH (NOLOCK) WHERE name ='CommservSurveyRunning'
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	SELECT @frequencyMode = mode from #MetricsInputParams

IF @queryId = 72 AND @isPrivateRunning = 'Diagnostics and Usage' 
	SET @isPrivateRunning = 'Metrics Reporting'

DECLARE @OperationType Int = 0 --Public
IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1')) --Private
	SELECT @OperationType = 1 
IF (@isPrivateRunning = 'Metrics Direct Dip')
	SELECT @OperationType = 2


DECLARE @ChargebackServiceFlags INT = 0
SELECT  @ChargebackServiceFlags = flags&(4|8|16|32|64|128)
FROM    CloudMonitoringService WITH (NOLOCK) 
WHERE   name = 'ChargeBack'

IF  @ChargebackServiceFlags = 0 AND @Type = @CHARGEBACK_TYPE_MONTHLY
	SET @ChargebackServiceFlags = (CASE WHEN @OperationType IN (1, 2) THEN 16 ELSE 128 END)

IF  @OperationType IN (1, 2) AND @ChargebackServiceFlags&(CASE @queryId WHEN 72 THEN  4 WHEN 162 THEN  8 ELSE  16 END) = 0
 OR @OperationType = 0 AND @ChargebackServiceFlags&(CASE @queryId WHEN 72 THEN 32 WHEN 162 THEN 64 ELSE 128 END) = 0
BEGIN
	SET @ErrorCode = 1
	GOTO FINISH
END

-- Use custom calendar for monthly chargeback if exists
DECLARE	@CalendarId	INT = -1
SELECT	@CalendarId = id FROM APP_CustomCalendar WITH (NOLOCK) WHERE name = 'Chargeback'

DECLARE	@FromDateTime   DATETIME
DECLARE	@ToDateTime     DATETIME
DECLARE	@TimeRangeBegin	INT = 0
DECLARE	@TimeRangeEnd	INT = 0x7FFFFFFF
DECLARE @sqlstr			NVARCHAR(MAX)
DECLARE @TimeStampName	VARCHAR(64)
DECLARE	@FromDailyData	INT = 0

CREATE TABLE #UsageDate (fromDT DATETIME, toDT DATETIME, startDT DATETIME, LastDayDT DATETIME)

IF @Type = @CHARGEBACK_TYPE_MONTHLY
BEGIN
	DECLARE @CurrentMonth	DATETIME
	IF @CalendarId > 1 
		SELECT @CurrentMonth = startDate FROM APP_CustomCalendarMonths WITH (NOLOCK)
		WHERE calendarId = @CalendarId AND startDate < GETDATE() AND endDate >= GETDATE()

	IF @CurrentMonth IS NULL
		SET @CurrentMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

	SET	@FromDateTime = DATEADD(MONTH, -1, @CurrentMonth)
	SET	@ToDateTime   = DATEADD(SECOND, -1, @CurrentMonth)

	SET @TimeStampName = 'LastChargebackSizeCollectedMonth'
	IF @OperationType = 0
		SET @TimeStampName = 'LastChargebackSizeCollectedMonthPublic'
	ELSE IF @OperationType = 2
		SET @TimeStampName = 'LastChargebackSizeCollectedMonthDirectDip'	
END
ELSE IF @Type = @CHARGEBACK_TYPE_WEEKLY
BEGIN
	DECLARE @CurrentWeek    DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
	SET	@CurrentWeek  = DATEADD(DAY, 1-DATEPART(WEEKDAY, @CurrentWeek), @CurrentWeek)
	SET	@FromDateTime = DATEADD(WEEK, -1, @CurrentWeek)
	SET	@ToDateTime   = DATEADD(SECOND, -1, @CurrentWeek)

	SET @TimeStampName = 'LastChargebackSizeCollectedWeek'
	IF @OperationType = 0
		SET @TimeStampName = 'LastChargebackSizeCollectedWeekPublic'
	ELSE IF @OperationType = 2
		SET @TimeStampName = 'LastChargebackSizeCollectedWeekDirectDip'
END
ELSE IF @Type = @CHARGEBACK_TYPE_DAILY
BEGIN
	DECLARE @CurrentDay     DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
	SET	@FromDateTime = DATEADD(DAY, -1, @CurrentDay)
	SET	@ToDateTime   = DATEADD(SECOND, -1, @CurrentDay)

	SET @TimeStampName = 'LastChargebackSizeCollectedDay'
	IF @OperationType = 0
		SET @TimeStampName = 'LastChargebackSizeCollectedDayPublic'
	ELSE IF @OperationType = 2
		SET @TimeStampName = 'LastChargebackSizeCollectedDayDirectDip'
END

DECLARE @LastCollectedPeriod DATETIME

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @LastCollectedPeriodSTR VARCHAR(256)='';
	EXEC GetSurveyParamValue @TimeStampName, @LastCollectedPeriodSTR OUTPUT 
	SELECT @LastCollectedPeriod = CONVERT(DATETIME, @LastCollectedPeriodSTR, 121)
END
ELSE
BEGIN
	SELECT	@LastCollectedPeriod = CONVERT(DATETIME, CAST(value AS VARCHAR(32)), 121)
	FROM	GXGlobalParam WITH (NOLOCK)
	WHERE	name = @TimeStampName
END

IF @LastCollectedPeriod IS NOT NULL
BEGIN
	DECLARE @PeriodDiff INT = 0
	SET @PeriodDiff = CASE @Type 
		WHEN @CHARGEBACK_TYPE_MONTHLY THEN DATEDIFF(MONTH, @LastCollectedPeriod, @FromDateTime)
		WHEN @CHARGEBACK_TYPE_WEEKLY THEN DATEDIFF(WEEK, @LastCollectedPeriod, @FromDateTime)
		WHEN @CHARGEBACK_TYPE_DAILY THEN DATEDIFF(DAY, @LastCollectedPeriod, @FromDateTime)
		ELSE 0 END

	IF @PeriodDiff <= 0
	BEGIN
		SET @ErrorCode = 1
		GOTO FINISH
	END
	ELSE
	IF @PeriodDiff > 1
	BEGIN
		SET @FromDateTime = CASE @Type 
			WHEN @CHARGEBACK_TYPE_MONTHLY THEN DATEADD(MONTH, 1, @LastCollectedPeriod)
			WHEN @CHARGEBACK_TYPE_WEEKLY THEN DATEADD(WEEK, 1, @LastCollectedPeriod)
			WHEN @CHARGEBACK_TYPE_DAILY THEN DATEADD(DAY, 1, @LastCollectedPeriod)
			ELSE 0 END

		SET @ToDateTime = CASE @Type 
			WHEN @CHARGEBACK_TYPE_MONTHLY THEN DATEADD(MONTH, 1, @FromDateTime)
			WHEN @CHARGEBACK_TYPE_WEEKLY THEN DATEADD(WEEK, 1, @FromDateTime)
			WHEN @CHARGEBACK_TYPE_DAILY THEN DATEADD(DAY, 1, @FromDateTime)
			ELSE 0 END

		SET	@ToDateTime = DATEADD(SECOND, -1, @ToDateTime)
	END
END
ELSE
IF OBJECT_ID('RptCapacityUsage', 'U') IS NOT NULL AND OBJECT_ID('RptStorageUsage', 'U') IS NOT NULL
BEGIN
	SET @sqlstr = CASE @Type 
		WHEN @CHARGEBACK_TYPE_MONTHLY THEN N'INSERT INTO #UsageDate SELECT MIN(startDT), NULL, NULL, NULL FROM RptStorageUsage WHERE type = 2'
		WHEN @CHARGEBACK_TYPE_WEEKLY THEN N'INSERT INTO #UsageDate SELECT MIN(startDT), NULL, NULL, NULL FROM RptStorageUsage WHERE type IN (1,3)'
		WHEN @CHARGEBACK_TYPE_DAILY THEN N'INSERT INTO #UsageDate SELECT MIN(startDT), NULL, NULL, NULL FROM RptStorageUsage WHERE type = 1'
		END
	EXEC sp_executesql @sqlstr
	
	IF EXISTS (SELECT * FROM #UsageDate WHERE fromDT IS NOT NULL)
	BEGIN
		SELECT @FromDateTime = fromDT FROM #UsageDate
	
		SET @ToDateTime = CASE @Type 
				WHEN @CHARGEBACK_TYPE_MONTHLY THEN DATEADD(SECOND, -1, DATEADD(MONTH, 1, @FromDateTime))
				WHEN @CHARGEBACK_TYPE_WEEKLY THEN DATEADD(SECOND, -1, DATEADD(WEEK, 1, @FromDateTime))
				WHEN @CHARGEBACK_TYPE_DAILY THEN DATEADD(SECOND, -1, DATEADD(DAY, 1, @FromDateTime))
			END
	END
	
	DELETE FROM #UsageDate
END

INSERT	INTO #UsageDate
SELECT	@FromDateTime, @ToDateTime, NULL, NULL

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


SET @TimeRangeBegin = dbo.GetUnixTime(dbo.LocalToUTCTime(@FromDateTime, @csTimeZone))
SET @TimeRangeEnd   = dbo.GetUnixTime(dbo.LocalToUTCTime(@ToDateTime, @csTimeZone))

--------------------------------------------
-- If data are available in RptStorageUsage 
-- and RptCapacityUsage tables
--------------------------------------------
IF OBJECT_ID('RptCapacityUsage', 'U') IS NOT NULL AND OBJECT_ID('RptStorageUsage', 'U') IS NOT NULL
BEGIN
	SET @sqlstr = N'UPDATE #UsageDate SET lastDayDT = (SELECT MAX(startDT) FROM RptStorageUsage)'
	EXEC sp_executesql @sqlstr

	IF NOT EXISTS (SELECT * FROM #UsageDate WHERE DATEDIFF(DAY, @ToDateTime, ISNULL(lastDayDT, '01/01/1970')) >= 0)
		BEGIN
			SET @ErrorCode = 1
			GOTO FINISH	
		END	

	IF @Type = @CHARGEBACK_TYPE_MONTHLY
	BEGIN
		SET @sqlstr = N'
		DECLARE @FromDate DATETIME = (SELECT fromDT FROM #UsageDate)
		UPDATE	#UsageDate SET startDT = (
			SELECT	MIN(T.startDT)
			FROM	(SELECT DISTINCT startDT FROM RptStorageUsage WITH (NOLOCK) WHERE type = 2) T
			WHERE	DATEDIFF(MONTH, @FromDate, T.startDT) >= 0)'
		
		EXEC sp_executesql @sqlstr
		
		IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
		BEGIN
			IF COL_LENGTH('RptStorageUsage', 'backupAppSize') IS NOT NULL
				SET @sqlstr = N'
				INSERT	INTO #TblResults
				SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, backupAppSize
				FROM	RptStorageUsage WITH (NOLOCK)
				WHERE	type = 2 AND startDT = (SELECT startDT FROM #UsageDate)'
			ELSE
				SET @sqlstr = N'
				INSERT	INTO #TblResults
				SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, NULL
				FROM	RptStorageUsage WITH (NOLOCK)
				WHERE	type = 2 AND startDT = (SELECT startDT FROM #UsageDate)'

			IF COL_LENGTH('RptCapacityUsage', 'applicationSize') IS NOT NULL
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.applicationSize
				FROM	#TblResults T INNER JOIN (
					SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize, MAX(applicationSize) AS applicationSize
					FROM	RptCapacityUsage WITH (NOLOCK)
					WHERE	VMClientId = 0 AND type = 2 AND startDT = (SELECT startDT FROM #UsageDate)
					GROUP BY appId
					) S ON T.AppId = S.appId AND T.VMClientId = 0'
			ELSE
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
				FROM	#TblResults T INNER JOIN (
					SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
					FROM	RptCapacityUsage WITH (NOLOCK)
					WHERE	VMClientId = 0 AND type = 2 AND startDT = (SELECT startDT FROM #UsageDate)
					GROUP BY appId
					) S ON T.AppId = S.appId AND T.VMClientId = 0'
			
			SET @sqlstr += N'
			UPDATE	#TblResults
			SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
			FROM	#TblResults T INNER JOIN (
				SELECT	VMClientId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
				FROM	RptCapacityUsage WITH (NOLOCK)
				WHERE	VMClientId > 0 AND type = 2 AND startDT = (SELECT startDT FROM #UsageDate)
				GROUP BY VMClientId
				) S ON T.VMClientId = S.VMClientId'
			
			EXEC sp_executesql @sqlstr	
		END
		ELSE
		BEGIN
			SET @ErrorCode = 1
			GOTO FINISH
		END
	END
	ELSE
	IF @Type = @CHARGEBACK_TYPE_WEEKLY
	BEGIN
		SET @sqlstr = N'
		DECLARE @FromDate DATETIME = (SELECT fromDT FROM #UsageDate)
		UPDATE	#UsageDate SET startDT = (
			SELECT	MIN(T.startDT)
			FROM	(SELECT DISTINCT startDT FROM RptStorageUsage WITH (NOLOCK) WHERE type = 3) T
			WHERE	DATEDIFF(WEEK, @FromDate, T.startDT) >= 0)'

		EXEC sp_executesql @sqlstr

		IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
		BEGIN
			IF COL_LENGTH('RptStorageUsage', 'backupAppSize') IS NOT NULL
				SET @sqlstr = N'
				INSERT	INTO #TblResults
				SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, backupAppSize
				FROM	RptStorageUsage WITH (NOLOCK)
				WHERE	type = 3 AND startDT = (SELECT startDT FROM #UsageDate)'
			ELSE
				SET @sqlstr = N'
				INSERT	INTO #TblResults
				SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, NULL
				FROM	RptStorageUsage WITH (NOLOCK)
				WHERE	type = 3 AND startDT = (SELECT startDT FROM #UsageDate)'
			
			IF COL_LENGTH('RptCapacityUsage', 'applicationSize') IS NOT NULL
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.applicationSize
				FROM	#TblResults T INNER JOIN (
					SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize, MAX(applicationSize) AS applicationSize
					FROM	RptCapacityUsage WITH (NOLOCK)
					WHERE	VMClientId = 0 AND type = 3 AND startDT = (SELECT startDT FROM #UsageDate)
					GROUP BY appId
					) S ON T.AppId = S.appId AND T.VMClientId = 0'
			ELSE
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
				FROM	#TblResults T INNER JOIN (
					SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
					FROM	RptCapacityUsage WITH (NOLOCK)
					WHERE	VMClientId = 0 AND type = 3 AND startDT = (SELECT startDT FROM #UsageDate)
					GROUP BY appId
					) S ON T.AppId = S.appId AND T.VMClientId = 0'
			
			SET @sqlstr += N'
			UPDATE	#TblResults
			SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
			FROM	#TblResults T INNER JOIN (
				SELECT	VMClientId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
				FROM	RptCapacityUsage WITH (NOLOCK)
				WHERE	VMClientId > 0 AND type = 3 AND startDT = (SELECT startDT FROM #UsageDate)
				GROUP BY VMClientId
				) S ON T.VMClientId = S.VMClientId'
			
			EXEC sp_executesql @sqlstr	
		END
		ELSE
		BEGIN
			SET @sqlstr = N'
			UPDATE	#UsageDate SET startDT = fromDT
			WHERE	EXISTS (
				SELECT TOP 1 S.startDT 
				FROM RptStorageUsage S WITH (NOLOCK)
					INNER JOIN #UsageDate T ON S.startDT >= T.fromDT AND S.startDT < T.toDT
				WHERE S.type = 1)'

			EXEC sp_executesql @sqlstr
		END

		IF NOT EXISTS (SELECT * FROM #TblResults)
		BEGIN
			IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
			BEGIN
				SET @sqlstr = N'
				DECLARE @fromDT DATETIME = (SELECT startDT FROM #UsageDate) 
				DECLARE	@toDT   DATETIME = DATEADD(WEEK, 1, @fromDT)
				DECLARE	@lastDT DATETIME = DATEADD(DAY, -1, @toDT)

				SELECT  @lastDT = MAX(startDT) FROM RptStorageUsage WHERE startDT >= @fromDT AND startDT < @toDT AND type = 1'

				IF COL_LENGTH('RptStorageUsage', 'backupAppSize') IS NOT NULL
				SET @sqlstr += N'
				INSERT	INTO #TblResults
				SELECT	@fromDT, U.vmClientId, U.appId, U.copyId, U.allAppSize, U.allMediaSize, ISNULL(S.dataProtected, 0), 
						CASE WHEN U.dedupRatio > 0 AND U.dedupRatio < 1 THEN ISNULL(S.dataProtected, 0)*U.dedupRatio ELSE ISNULL(S.dataWritten, 0) END, 
						0, 0, 0, U.backupAppSize'
				ELSE
				SET @sqlstr += N'
				INSERT	INTO #TblResults
				SELECT	@fromDT, U.vmClientId, U.appId, U.copyId, U.allAppSize, U.allMediaSize, ISNULL(S.dataProtected, 0), 
						CASE WHEN U.dedupRatio > 0 AND U.dedupRatio < 1 THEN ISNULL(S.dataProtected, 0)*U.dedupRatio ELSE ISNULL(S.dataWritten, 0) END, 
						0, 0, 0, NULL'

				SET @sqlstr += N'
				FROM	RptStorageUsage U WITH (NOLOCK)
						LEFT OUTER JOIN (
						SELECT	vmClientId, appId, copyId, SUM(dataProtected) AS dataProtected, SUM(dataWritten) AS dataWritten
						FROM	RptStorageUsage WITH (NOLOCK)
						WHERE	startDT >= @fromDT AND startDT < @toDT AND type = 1
						GROUP BY vmClientId, appId, copyId
						) S ON U.appId = S.appId AND U.vmClientId = S.vmClientId AND U.copyId = S.copyId 
				WHERE	U.startDT = @LastDT AND U.type = 1'
			
				IF COL_LENGTH('RptCapacityUsage', 'applicationSize') IS NOT NULL
					SET @sqlstr += N'
					UPDATE	#TblResults
					SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.applicationSize
					FROM	#TblResults T INNER JOIN 
							(SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize, MAX(applicationSize) AS applicationSize
							FROM	RptCapacityUsage
							WHERE	vmClientId = 0 AND startDT >= @fromDT AND startDT < @toDT AND type = 1
							GROUP BY appId
							) S ON T.appId = S.appId AND T.vmClientId = 0'
				ELSE
					SET @sqlstr += N'
					UPDATE	#TblResults
					SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
					FROM	#TblResults T INNER JOIN 
							(SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
							FROM	RptCapacityUsage
							WHERE	vmClientId = 0 AND startDT >= @fromDT AND startDT < @toDT AND type = 1
							GROUP BY appId
							) S ON T.appId = S.appId AND T.vmClientId = 0'
			
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
				FROM	#TblResults T INNER JOIN 
						(SELECT	vmClientId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
						FROM	RptCapacityUsage
						WHERE	vmClientId = > AND startDT >= @fromDT AND startDT < @toDT AND type = 1
						GROUP BY vmClientId
						) S ON T.vmClientId = S.vmClientId'	
			
				EXEC sp_executesql @sqlstr
			END
			ELSE
			BEGIN
				SET @ErrorCode = 1
				GOTO FINISH
			END
		END
	END
	ELSE
	IF @Type = @CHARGEBACK_TYPE_DAILY
	BEGIN
		SET @sqlstr = N'
		DECLARE @FromDate DATETIME = (SELECT fromDT FROM #UsageDate)
		UPDATE	#UsageDate SET startDT = (
			SELECT	MIN(T.startDT)
			FROM	(SELECT DISTINCT startDT FROM RptStorageUsage WITH (NOLOCK) WHERE type = 1) T
			WHERE	DATEDIFF(DAY, @FromDate, T.startDT) >= 0)'
		
		EXEC sp_executesql @sqlstr

		IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
		BEGIN
			SET @sqlstr = N'
			DECLARE @fromDT DATETIME = (SELECT startDT FROM #UsageDate)
			DECLARE	@toDT   DATETIME = DATEADD(DAY, 1, @fromDT)'

			IF COL_LENGTH('RptStorageUsage', 'backupAppSize') IS NOT NULL
				SET @sqlstr += N'
				INSERT	INTO #TblResults
				SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, backupAppSize
				FROM	RptStorageUsage WITH (NOLOCK)
				WHERE	startDT >= @fromDT AND startDT < @toDT AND type = 1'
			ELSE
				SET @sqlstr += N'
				INSERT	INTO #TblResults
				SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, NULL
				FROM	RptStorageUsage WITH (NOLOCK)
				WHERE	startDT >= @fromDT AND startDT < @toDT AND type = 1'
			
			IF COL_LENGTH('RptCapacityUsage', 'applicationSize') IS NOT NULL
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.applicationSize
				FROM	#TblResults T INNER JOIN (
					SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize, MAX(applicationSize) AS applicationSize
					FROM	RptCapacityUsage WITH (NOLOCK)
					WHERE	VMClientId = 0 AND startDT >= @fromDT AND startDT < @toDT AND type = 1
					GROUP BY appId
					) S ON T.AppId = S.appId AND T.VMClientId = 0'
			ELSE
				SET @sqlstr += N'
				UPDATE	#TblResults
				SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
				FROM	#TblResults T INNER JOIN (
					SELECT	appId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
					FROM	RptCapacityUsage WITH (NOLOCK)
					WHERE	VMClientId = 0 AND startDT >= @fromDT AND startDT < @toDT AND type = 1
					GROUP BY appId
					) S ON T.AppId = S.appId AND T.VMClientId = 0'
			
			SET @sqlstr += N'
			UPDATE	#TblResults
			SET		FrontendSize = S.frontendSize, FrontEndSizeType = S.jobType, ApplicationSize = S.frontendSize
			FROM	#TblResults T INNER JOIN (
				SELECT	VMClientId, MIN(jobType) AS jobType, MAX(frontendSize) AS frontendSize
				FROM	RptCapacityUsage WITH (NOLOCK)
				WHERE	VMClientId > 0 AND startDT >= @fromDT AND startDT < @toDT AND type = 1
				GROUP BY VMClientId
				) S ON T.VMClientId = S.VMClientId'
			
			EXEC sp_executesql @sqlstr
		END
		ELSE
		BEGIN
			SET @ErrorCode = 1
			GOTO FINISH
		END
	END

	IF	EXISTS (SELECT * FROM #TblResults WHERE TotalDataOnMedia IS NULL)
		TRUNCATE TABLE #TblResults
	ELSE
		GOTO SET_SURVEY_XML
END

--------------------------------------------
-- Retrieve storage policy copy IDs       --
--------------------------------------------

INSERT INTO #TblCopyIds
SELECT	AG.id, AG.name, AGC.id, AGC.name, AGC.type, 
	CASE WHEN AGC.id IN (AG.defaultCopy, AG.defaultSnapCopy) THEN 1 ELSE 0 END,
	AGC.isSnapCopy, AGC.dedupeFlags,
	CASE WHEN ((AGC.dedupeFlags & 262144) > 0) THEN 'Dedup' WHEN AGC.isSnapCopy > 0 THEN 'Snap' ELSE '' END
FROM	archGroup AG 
		INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AGC.archGroupId = AG.id
WHERE	AG.type <> 4 AND AGC.id > 0 AND AGC.type IN (1, 2, 3)		

UPDATE	#TblCopyIds 
SET		StorageType = CASE WHEN DPL.DrivePoolType = 10001 THEN 'Disk' ELSE 'Tape' END
FROM	#TblCopyIds C
		INNER JOIN MMDataPath DPT WITH (NOLOCK) ON DPT.CopyId = C.CopyId AND C.StorageType = ''
		INNER JOIN MMDrivePool DPL WITH (NOLOCK) ON DPT.DrivePoolId = DPL.DrivePoolId AND (DPT.Flag & 1) = 1

UPDATE	#TblCopyIds 
SET		StorageType = CASE WHEN T.RecordingFormatId = 10001 THEN 'Disk' ELSE 'Tape' END
FROM	(SELECT	TOP 1 C.CopyId, V.RecordingFormatId
		FROM	#TblCopyIds C
				INNER JOIN archStream S WITH (NOLOCK) ON S.archGroupCopyId = C.CopyId AND C.StorageType = ''
				INNER JOIN MMVolume V WITH (NOLOCK) ON V.MediaGroupId = S.mediaGroupId
		) T
WHERE	#TblCopyIds.CopyId = T.CopyId


--------------------------------------------
-- 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)
)
	
IF OBJECT_ID('DedupCopySaving', 'P') IS NOT NULL
	EXEC DedupCopySaving 0
ELSE
BEGIN
	INSERT	INTO #DedupeCopy
	SELECT	T.CopyId, CS.SIDBStoreId, T.dedupeFlags, 0, 0, 0, 0
	FROM	#TblCopyIds T INNER JOIN archCopySIDBStore CS ON T.CopyId = CS.CopyId
							INNER JOIN IdxSIDBStore I ON CS.SIDBStoreId = I.SIDBStoreId 
	WHERE	StorageType = 'Dedup'
			AND I.SealedTime = 0

	-- Total Application Size of fully and partially 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
	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
	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
	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
	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*(1000*T.dataCompressed/totalBackupSize)/1000 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	IdxSIDBStore S
				INNER JOIN (
					SELECT	SIDBStoreAliasName, MAX(SIDBStoreId) AS ActiveStoreId
					FROM	IdxSIDBStore WITH (NOLOCK)
					WHERE	SIDBStoreId > 0
					GROUP BY SIDBStoreAliasName
				) SN ON S.SIDBStoreAliasName = SN.SIDBStoreAliasName
				INNER JOIN (
					SELECT	SIDBStoreId, SUM(CAST(V.PhysicalBytesMB AS BIGINT)) AS PhysicalBytesMB
					FROM	MMVolume V WITH (NOLOCK)
					WHERE	SIDBStoreId > 0 AND SiloStatus <> 3 AND (Attributes & 512) = 0
					GROUP BY SIDBStoreId
				) SS ON S.SIDBStoreId = SS.SIDBStoreId
		GROUP BY ActiveStoreId
	)
	UPDATE	#DedupeCopy
	SET		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
--------------------------------------------
-- End of Calculation of average dedup ratio
--------------------------------------------

--------------------------------------------
-- Total Protected App Size/Size on Media --
--------------------------------------------

-- Subclient sizes on each copies
CREATE TABLE #AppCopySize (VMClientId INT, appId INT, archGrpCopyId INT, dataRead BIGINT, dataWritten BIGINT)

-- DR Backup Jobs
INSERT INTO #AppCopySize
SELECT	0, J.appId, J.archGrpCopyId, SUM(A.totalBackupSize), SUM(A.totalBackupSize)
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
		INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId AND J.dataType = 1
WHERE	A.servEnd <= @TimeRangeEnd AND A.totalBackupSize > 0
	AND J.status IN (100, 102, 103) AND (J.disabled & 256) = 0
GROUP BY J.appId, J.archGrpCopyId

-- Log Only jobs
INSERT INTO #AppCopySize
SELECT	0, J.appId, J.archGrpCopyId, SUM(B.totalUncompBytes), SUM(B.totalBackupSize)
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 4
			AND B.dataBackedUp = 0 AND B.logArchGrpId = J.archGrpId
WHERE	B.servEndDate <= @TimeRangeEnd AND B.totalBackupSize > 0
	AND J.status IN (100, 102, 103) AND (J.disabled & 256) = 0
GROUP BY J.appId, J.archGrpCopyId

-- Other jobs
INSERT INTO #AppCopySize
SELECT	0, J.appId, J.archGrpCopyId, SUM(B.totalUncompBytes), SUM(B.totalBackupSize)
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 1
			AND B.dataArchGrpId = J.archGrpId
WHERE	B.servEndDate <= @TimeRangeEnd AND B.totalBackupSize > 0
	AND J.status IN (100, 102, 103) AND (J.disabled & 256) = 0
GROUP BY J.appId, J.archGrpCopyId


-- Partially copied jobs before the end of time range
CREATE TABLE #PartialJobs (appId INT, appType INT, jobId INT, commCellId INT, archGrpCopyId INT, 
		totalAppSize BIGINT, totalBackupSize BIGINT, dataRead BIGINT, dataCompressed BIGINT)

INSERT	INTO #PartialJobs
SELECT	J.appId, 0, J.jobId, J.commCellId, J.archGrpCopyId, 0, 0, 0, 0
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
WHERE	J.status <> 1000 AND (J.disabled & 256) = 0
GROUP BY J.appId, J.jobId, J.commCellId, J.archGrpCopyId
HAVING MAX(J.status) IN (102, 103) OR MAX(J.status) = 101 AND MIN(J.status) = 100
	OR MAX(J.status) = 100 AND MIN(J.status) = 100 AND MAX(J.copiedTime) > @TimeRangeEnd

UPDATE	#PartialJobs
SET		appType = J.appType, totalAppSize = J.totalUncompBytes, totalBackupSize = J.totalBackupSize
FROM	JMBkpStats J WITH (NOLOCK)
WHERE	#PartialJobs.jobId = J.jobId AND #PartialJobs.commCellId = J.commCellId
	AND J.servEndDate <= @TimeRangeEnd AND J.totalBackupSize > 0

UPDATE	#PartialJobs
SET		totalAppSize = J.totalBackupSize, totalBackupSize = J.totalBackupSize
FROM	JMAdminJobStatsTable J WITH (NOLOCK)
WHERE	#PartialJobs.jobId = J.jobId AND #PartialJobs.commCellId = J.commCellId
	AND J.servEnd <= @TimeRangeEnd AND J.totalBackupSize > 0

INSERT INTO #AppCopySize
SELECT	0, appId, archGrpCopyId, -(totalAppSize - dataRead), -(totalBackupSize - dataCompressed)
FROM	(
	SELECT	J.appId, J.archGrpCopyId, J.totalAppSize, J.totalBackupSize, 
			SUM((1.0*ACM.physicalSize/J.totalBackupSize)*J.totalAppSize) AS dataRead, 
			SUM(ACM.physicalSize) AS dataCompressed
	FROM	#PartialJobs J
			INNER JOIN archFile AF WITH (NOLOCK) ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AF.id = ACM.archFileId AND AF.commCellId = ACM.commCellId AND J.archGrpCopyId = ACM.archCopyId
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
	WHERE	AC.createTime <= @TimeRangeEnd AND J.totalBackupSize > 0
	GROUP BY J.appId, J.archGrpCopyId, J.totalAppSize, J.totalBackupSize) T


-- Retrieve application size of VMs
CREATE	TABLE #VMSize (jobId INT, commCellId INT, appId INT, bkpLevel INT, VMclientId INT, dataRead BIGINT)
CREATE	TABLE #VMJobSize (jobId INT, commCellId INT, dataRead BIGINT)
CREATE	TABLE #VMJobCopy (jobId INT, commCellId INT, archGrpCopyId INT, isSnapCopy INT, appId INT, bkpLevel INT, 
		totalUncompBytes BIGINT, totalBackupSize BIGINT, CompBytes BIGINT)
CREATE	TABLE #SynthFullJobVM (jobId INT, commCellId INT, AppId INT, BkpLevel INT)

INSERT INTO #VMJobCopy
SELECT	J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize, B.totalBackupSize
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId 
			AND B.appType = 106 AND B.status IN (1, 3, 14) AND B.servEndDate <= @TimeRangeEnd
		INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
WHERE	J.status IN (100, 102, 103) AND (J.disabled & 256) = 0
GROUP BY J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize

CREATE INDEX VMJobCopy_Idx ON #VMJobCopy (jobId, commCellId)

IF EXISTS (SELECT jobId FROM #PartialJobs WHERE appType = 106)
BEGIN
	UPDATE	#VMJobCopy SET totalUncompBytes = PJ.totalAppSize, totalBackupSize = PJ.totalBackupSize, CompBytes = PJ.dataCompressed
	FROM	#PartialJobs PJ
	WHERE	#VMJobCopy.jobId = PJ.jobId AND #VMJobCopy.commCellId = PJ.commCellId AND #VMJobCopy.archGrpCopyId = PJ.archGrpCopyId AND PJ.appType = 106
END
DROP TABLE #PartialJobs

INSERT	INTO #VMSize
SELECT	DISTINCT V.jobId, V.commCellId, J.appId, J.bkpLevel, V.VMclientId, CAST(V.attrVal AS BIGINT)
FROM	APP_VMProp V WITH (NOLOCK)
		INNER JOIN #VMJobCopy J ON V.jobId = J.jobId AND V.commCellId = J.commCellId
WHERE	V.attrName = 'vmUsedSpace' AND V.attrVal <> '0'

DELETE	#VMSize
FROM	APP_VMProp V WITH (NOLOCK)
WHERE	#VMSize.jobId = V.jobId AND #VMSize.commCellId = V.commCellId AND #VMSize.VMclientId = V.VMclientId
	AND V.attrName = 'vmStatus' AND V.attrVal NOT IN ('0', '3')

-- vmUsedSpace may not be set for Synthetic Full jobs
INSERT INTO #SynthFullJobVM
SELECT DISTINCT JC.jobId, JC.commCellId, JC.appId, JC.bkpLevel 
FROM	#VMJobCopy JC
		LEFT OUTER JOIN #VMSize S ON JC.jobId = S.jobId AND JC.commCellId = S.commCellId AND S.VMclientId > 0
WHERE	JC.bkpLevel IN (64, 128, 16384) AND S.VMclientId IS NULL

IF EXISTS (SELECT * FROM #SynthFullJobVM)
BEGIN
	; WITH LastFullJobPerVM (appId, jobId, commCellId, BkpLevel, FullJobId) AS 
	(
		SELECT	J.AppId, J.jobId, J.commCellId, J.BkpLevel, MAX(B.jobId)
		FROM	#SynthFullJobVM J
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.appId = J.AppId
		WHERE	B.bkpLevel IN (1, 1024, 32768) AND B.status IN (1, 14)
			AND B.jobId < J.jobId AND B.commCellId = J.commCellId
		GROUP BY J.AppId, J.jobId, J.commCellId, J.BkpLevel
	)
	INSERT INTO #VMSize 
	SELECT	J.jobId, J.commCellId, J.BkpLevel, V.VMclientId, 0, MAX(CAST(V.attrVal AS BIGINT)) AS vmUsedSpace
	FROM	LastFullJobPerVM J
			INNER JOIN JMBkpStats B WITH (NOLOCK) 
				ON B.appId = J.AppId AND B.jobId >= J.FullJobId AND B.jobId < J.jobId AND B.commCellId = J.commCellId
			INNER JOIN APP_VMProp V WITH (NOLOCK) 
				ON V.jobId = B.JobId AND V.commCellId = B.commCellId AND V.attrName = 'vmUsedSpace'
	GROUP BY J.jobId, J.commCellId, J.BkpLevel, V.VMclientId
END

INSERT INTO #VMJobSize
SELECT	jobId, commCellId, SUM(dataRead)
FROM	#VMSize
GROUP BY jobId, commCellId

INSERT	INTO #AppCopySize
SELECT	ISNULL(V.VMclientId, 0), S.appId, S.archGrpCopyId, 
		SUM((CASE WHEN S.isSnapCopy = 0 THEN 1.0*S.CompBytes/S.totalBackupSize ELSE 1.0 END)*ISNULL(1.0*V.dataRead/J.dataRead, 1)*S.totalUncompBytes), 
		SUM(ISNULL(1.0*V.dataRead/J.dataRead, 1)*S.CompBytes)
FROM	#VMJobCopy S WITH (NOLOCK)
		LEFT OUTER JOIN #VMJobSize J ON S.jobId = J.jobId AND S.commCellId = J.commCellId AND J.dataRead > 0
		LEFT OUTER JOIN #VMSize V ON J.jobId = V.jobId AND J.commCellId = V.commCellId
WHERE	S.totalBackupSize > 0
GROUP BY ISNULL(V.VMclientId, 0), S.appId, S.archGrpCopyId

INSERT INTO #TblResults
SELECT	@FromDateTime, VMClientId, appId, archGrpCopyId, SUM(dataRead), SUM(dataWritten), 0, 0, 0, 0, 0, NULL
FROM	#AppCopySize 
GROUP BY VMClientId, appId, archGrpCopyId

UPDATE	#TblResults
SET		TotalDataOnMedia = TotalDataProtected * C.dedupRatio
FROM	#DedupeCopy C
WHERE	#TblResults.CopyId = C.archGrpCopyId

TRUNCATE TABLE #AppCopySize
TRUNCATE TABLE #VMSize
TRUNCATE TABLE #VMJobSize
TRUNCATE TABLE #VMJobCopy
TRUNCATE TABLE #SynthFullJobVM
--------------------------------------------
-- End of Total Proetected App Size/Size on Media
--------------------------------------------

--------------------------------------------
-- Protected App Size/Size on Media in last month/week/day
--------------------------------------------
CREATE TABLE #StorageUsage (appId INT, vmClientId INT, copyId INT, dedupRatio FLOAT, dataProtected BIGINT, dataWritten BIGINT)

IF OBJECT_ID('RptStorageUsage', 'U') IS NULL
BEGIN
	-- DR Backup Jobs
	INSERT INTO #AppCopySize
	SELECT	0, J.appId, J.archGrpCopyId, SUM(A.totalBackupSize), SUM(A.totalBackupSize)
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
			INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId AND J.dataType = 1
	WHERE	A.servEnd BETWEEN @TimeRangeBegin AND @TimeRangeEnd AND A.totalBackupSize > 0
		AND J.status = 100 AND J.copiedTime = 0 
	GROUP BY J.appId, J.archGrpCopyId

	-- Log Only jobs
	INSERT INTO #AppCopySize
	SELECT	0, J.appId, J.archGrpCopyId, SUM(B.totalUncompBytes), SUM(B.totalBackupSize)
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 4
				AND B.dataBackedUp = 0 AND B.logArchGrpId = J.archGrpId
	WHERE	B.servEndDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd AND B.totalBackupSize > 0 AND B.appType <> 106
		AND J.status = 100 AND J.copiedTime = 0 
	GROUP BY J.appId, J.archGrpCopyId

	-- Other jobs
	INSERT INTO #AppCopySize
	SELECT	0, J.appId, J.archGrpCopyId, SUM(B.totalUncompBytes), SUM(B.totalBackupSize)
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 1
				AND B.dataArchGrpId = J.archGrpId
	WHERE	B.servStartDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd AND B.totalBackupSize > 0 AND B.appType <> 106
		AND J.status = 100 AND J.copiedTime = 0 
	GROUP BY J.appId, J.archGrpCopyId

	INSERT INTO #AppCopySize
	SELECT	0, AF.appId, ACM.archCopyId, 
			SUM(CASE WHEN T.IsSnapCopy = 0 THEN (1.0*ACM.physicalSize/T.totalBackupSize)*T.totalUncompBytes ELSE T.totalUncompBytes END), 
			SUM(ACM.physicalSize)
	FROM	(SELECT J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, B.totalUncompBytes, B.totalBackupSize
			FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND B.appType <> 106
			WHERE	J.copiedTime > @TimeRangeBegin AND J.status IN (100, 102, 103)
				AND B.totalBackupSize > 0
			GROUP BY J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, B.totalUncompBytes, B.totalBackupSize
			) T
			INNER JOIN archFile AF WITH (NOLOCK) ON T.jobId = AF.jobId AND T.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AF.id = ACM.archFileId AND AF.commCellId = ACM.commCellId AND T.archGrpCopyId = ACM.archCopyId
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
	WHERE	AC.createTime BETWEEN @TimeRangeBegin AND @TimeRangeEnd
	GROUP BY AF.appId, ACM.archCopyId

	-- VM sizes
	INSERT INTO #VMJobCopy
	SELECT	J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize, B.totalBackupSize
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND B.appType = 106
				AND B.status IN (1, 3, 14) AND B.servEndDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
	WHERE	J.status = 100 AND J.copiedTime = 0 
		AND B.totalBackupSize > 0
	GROUP BY J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize

	INSERT INTO #VMJobCopy
	SELECT	J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize, B.totalBackupSize
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND B.appType = 106
				AND B.status IN (1, 3, 14) AND B.servEndDate < @TimeRangeEnd
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
	WHERE	J.status IN (100, 102, 103) AND J.copiedTime > @TimeRangeBegin 
		AND B.totalBackupSize > 0
	GROUP BY J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize

	INSERT INTO #VMJobCopy
	SELECT	J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize, SUM(ACM.physicalSize)
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #TblCopyIds C ON J.archGrpCopyId = C.CopyId
			INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND B.appType = 106
				AND B.status IN (1, 3, 14)
			INNER JOIN archFile AF WITH (NOLOCK) ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId 
				AND J.dataType = AF.fileType AND AF.isValid = 1
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AF.id = ACM.archFileId AND AF.commCellId = ACM.commCellId AND J.archGrpCopyId = ACM.archCopyId
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId 
				AND AC.createTime BETWEEN @TimeRangeBegin AND @TimeRangeEnd 
	WHERE	J.status IN (100, 102, 103) 
		AND J.copiedTime > @TimeRangeBegin 
		AND (B.servEndDate < @TimeRangeBegin OR B.servEndDate > @TimeRangeEnd OR J.copiedTime > @TimeRangeEnd)
		AND B.totalBackupSize > 0
	GROUP BY J.jobId, J.commCellId, J.archGrpCopyId, C.IsSnapCopy, J.appId, B.bkpLevel, B.totalUncompBytes, B.totalBackupSize

	INSERT	INTO #VMSize
	SELECT	DISTINCT V.jobId, V.commCellId, J.appId, J.bkpLevel, V.VMclientId, CAST(V.attrVal AS BIGINT)
	FROM	APP_VMProp V WITH (NOLOCK)
			INNER JOIN #VMJobCopy J ON V.jobId = J.jobId AND V.commCellId = J.commCellId
	WHERE	V.attrName = 'vmUsedSpace' AND V.attrVal <> '0'

	DELETE	#VMSize
	FROM	APP_VMProp V WITH (NOLOCK)
	WHERE	#VMSize.jobId = V.jobId AND #VMSize.commCellId = V.commCellId AND #VMSize.VMclientId = V.VMclientId
		AND V.attrName = 'vmStatus' AND V.attrVal NOT IN ('0', '3')

	-- vmUsedSpace may not be set for Synthetic Full jobs
	INSERT INTO #SynthFullJobVM
	SELECT DISTINCT JC.jobId, JC.commCellId, JC.appId, JC.bkpLevel 
	FROM	#VMJobCopy JC
			LEFT OUTER JOIN #VMSize S ON JC.jobId = S.jobId AND JC.commCellId = S.commCellId AND S.VMclientId > 0
	WHERE	JC.bkpLevel IN (64, 128, 16384) AND S.VMclientId IS NULL

	IF EXISTS (SELECT * FROM #SynthFullJobVM)
	BEGIN
		; WITH LastFullJobPerVM (appId, jobId, commCellId, BkpLevel, FullJobId) AS 
		(
			SELECT	J.AppId, J.jobId, J.commCellId, J.BkpLevel, MAX(B.jobId)
			FROM	#SynthFullJobVM J
					INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.appId = J.AppId
			WHERE	B.bkpLevel IN (1, 1024, 32768) AND B.status IN (1, 14)
				AND B.jobId < J.jobId AND B.commCellId = J.commCellId
			GROUP BY J.AppId, J.jobId, J.commCellId, J.BkpLevel
		)
		INSERT INTO #VMSize 
		SELECT	J.jobId, J.commCellId, J.BkpLevel, V.VMclientId, 0, MAX(CAST(V.attrVal AS BIGINT)) AS vmUsedSpace
		FROM	LastFullJobPerVM J
				INNER JOIN JMBkpStats B WITH (NOLOCK) 
					ON B.appId = J.AppId AND B.jobId >= J.FullJobId AND B.jobId < J.jobId AND B.commCellId = J.commCellId
				INNER JOIN APP_VMProp V WITH (NOLOCK) 
					ON V.jobId = B.JobId AND V.commCellId = B.commCellId AND V.attrName = 'vmUsedSpace'
		GROUP BY J.jobId, J.commCellId, J.BkpLevel, V.VMclientId
	END

	INSERT INTO #VMJobSize
	SELECT	jobId, commCellId, SUM(dataRead)
	FROM	#VMSize
	GROUP BY jobId, commCellId

	INSERT	INTO #AppCopySize
	SELECT	ISNULL(V.VMclientId, 0), S.appId, S.archGrpCopyId, 
			SUM((CASE WHEN S.isSnapCopy = 0 THEN 1.0*S.CompBytes/S.totalBackupSize ELSE 1.0 END)*ISNULL(1.0*V.dataRead/J.dataRead, 1)*S.totalUncompBytes), 
			SUM(ISNULL(1.0*V.dataRead/J.dataRead, 1)*S.CompBytes)
	FROM	#VMJobCopy S WITH (NOLOCK)
			LEFT OUTER JOIN #VMJobSize J ON S.jobId = J.jobId AND S.commCellId = J.commCellId AND J.dataRead > 0
			LEFT OUTER JOIN #VMSize V ON J.jobId = V.jobId AND J.commCellId = V.commCellId
	WHERE	S.totalBackupSize > 0
	GROUP BY ISNULL(V.VMclientId, 0), S.appId, S.archGrpCopyId	

	INSERT INTO #TblResults
	SELECT	@FromDateTime, VMClientId, appId, archGrpCopyId, SUM(backupAppSize), SUM(dataRead), SUM(dataWritten), 0, 0, 0, 0, NULL
	FROM	#AppCopySize 
	GROUP BY VMClientId, appId, archGrpCopyId

	UPDATE	#TblResults
	SET		DataWritten = DataProtected * C.dedupRatio
	FROM	#DedupeCopy C
	WHERE	#TblResults.CopyId = C.archGrpCopyId
END
ELSE
IF @Type = @CHARGEBACK_TYPE_MONTHLY
BEGIN
	SET @sqlstr = N'
	DECLARE @FromDate DATETIME = (SELECT fromDT FROM #UsageDate)
	UPDATE	#UsageDate SET startDT = (
		SELECT	MIN(T.startDT)
		FROM	(SELECT DISTINCT startDT FROM RptStorageUsage WITH (NOLOCK) WHERE type = 2) T
		WHERE	DATEDIFF(MONTH, @FromDate, T.startDT) >= 0)'
		
	EXEC sp_executesql @sqlstr
	
	IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
	BEGIN
		SET @sqlstr = N'
		UPDATE	#TblResults
		SET		DataProtected = S.dataProtected, DataWritten = S.dataWritten
		FROM	#TblResults T INNER JOIN RptStorageUsage S WITH (NOLOCK)
			 ON T.VMClientId = S.VMClientId AND T.AppId = S.appId AND T.CopyId = S.copyId
			AND S.type = 2 AND S.startDT = (SELECT startDT FROM #UsageDate)'
		
		EXEC sp_executesql @sqlstr	
	END
	ELSE
	BEGIN
			SET @ErrorCode = 1
			GOTO FINISH
	END	
END
ELSE
IF @Type = @CHARGEBACK_TYPE_WEEKLY
BEGIN
	SET @sqlstr = N'
	DECLARE @FromDate DATETIME = (SELECT fromDT FROM #UsageDate)
	UPDATE	#UsageDate SET startDT = (
		SELECT	MIN(T.startDT)
		FROM	(SELECT DISTINCT startDT FROM RptStorageUsage WITH (NOLOCK) WHERE type = 3) T
		WHERE	DATEDIFF(WEEK, @FromDate, T.startDT) >= 0)'
	
	EXEC sp_executesql @sqlstr
	
	IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
	BEGIN
		SET @sqlstr = N'
			DECLARE @fromDT DATETIME = (SELECT startDT FROM #UsageDate) 
			DECLARE	@toDT   DATETIME = DATEADD(WEEK, 1, @fromDT)
			DECLARE	@lastDT DATETIME = DATEADD(DAY, -1, @toDT)

			SELECT  @lastDT = MAX(startDT) FROM RptStorageUsage WHERE startDT >= @fromDT AND startDT < @toDT AND type = 1

			INSERT	INTO #TblResults
			SELECT	@fromDT, U.vmClientId, U.appId, U.copyId, U.allAppSize, U.allMediaSize, ISNULL(S.dataProtected, 0), 
					CASE WHEN U.dedupRatio > 0 AND U.dedupRatio < 1 THEN ISNULL(S.dataProtected, 0)*U.dedupRatio ELSE ISNULL(S.dataWritten, 0) END, 
					0, 0, 0, NULL
			FROM	RptStorageUsage U WITH (NOLOCK)
					LEFT OUTER JOIN (
					SELECT	vmClientId, appId, copyId, SUM(dataProtected) AS dataProtected, SUM(dataWritten) AS dataWritten
					FROM	RptStorageUsage WITH (NOLOCK)
					WHERE	startDT >= @fromDT AND startDT < @toDT AND type = 1
					GROUP BY vmClientId, appId, copyId
					) S ON U.appId = S.appId AND U.vmClientId = S.vmClientId AND U.copyId = S.copyId 
			WHERE	U.startDT = @LastDT AND U.type = 1'	
		
		EXEC sp_executesql @sqlstr
		
		UPDATE	#TblResults
		SET		DataProtected = S.dataProtected, DataWritten = S.dataWritten
		FROM	#TblResults T INNER JOIN #StorageUsage S WITH (NOLOCK)
			 ON T.VMClientId = S.VMClientId AND T.AppId = S.appId AND T.CopyId = S.copyId
	END
	ELSE
	BEGIN
			SET @ErrorCode = 1
			GOTO FINISH
	END
END
ELSE
IF @Type = @CHARGEBACK_TYPE_DAILY
BEGIN
	SET @sqlstr = N'
	DECLARE @FromDate DATETIME = (SELECT fromDT FROM #UsageDate)
	UPDATE	#UsageDate SET startDT = (
		SELECT	MIN(T.startDT)
		FROM	(SELECT DISTINCT startDT FROM RptStorageUsage WITH (NOLOCK) WHERE type = 1) T
		WHERE	DATEDIFF(DAY, @FromDate, T.startDT) >= 0)'
	
	EXEC sp_executesql @sqlstr

	IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
	BEGIN
		SET @sqlstr = N'
			DECLARE @fromDT DATETIME = (SELECT startDT FROM #UsageDate)
			DECLARE	@toDT   DATETIME = DATEADD(DAY, 1, @fromDT)

			INSERT	INTO #TblResults
			SELECT	startDT, vmClientId, appId, copyId, allAppSize, allMediaSize, dataProtected, dataWritten, 0, 0, 0, NULL
			FROM	RptStorageUsage WITH (NOLOCK)
			WHERE	startDT >= @fromDT AND startDT < @toDT AND type = 1'
		
		EXEC sp_executesql @sqlstr
		
		UPDATE	#TblResults
		SET		DataProtected = S.dataProtected, DataWritten = S.dataWritten
		FROM	#TblResults T INNER JOIN #StorageUsage S WITH (NOLOCK)
			 ON T.VMClientId = S.VMClientId AND T.AppId = S.appId AND T.CopyId = S.copyId
	END
	ELSE
	BEGIN
			SET @ErrorCode = 1
			GOTO FINISH
	END
END

DROP TABLE #AppCopySize
DROP TABLE #DedupeCopy
DROP TABLE #VMSize
DROP TABLE #VMJobSize
DROP TABLE #VMJobCopy
DROP TABLE #SynthFullJobVM
DROP TABLE #StorageUsage


--------------------------------------------
-- Get FrontEndSize --
--------------------------------------------
DECLARE @SpVersion BIGINT = 0
SELECT	@SpVersion = CAST(revision AS BIGINT) FROM GxDBVersions WITH(NOLOCK) where name = 'QS_CommCellUsageReport'  AND ISNUMERIC(revision) = 1

CREATE TABLE #CapacityUsage (
	EnterpriseBackupSize BIGINT, CoreBackupSize BIGINT, EnterpriseArchiveSize BIGINT, CoreArchiveSize BIGINT, 
	SnapshotSize BIGINT, ReplicationSize BIGINT, nJobType INT, 
	appId INT, jobID INT, ClientName NVARCHAR(255), ClientId INT, AppTypeId INT, AppTypeName VARCHAR(255), 
	BackupSetName NVARCHAR(128), InstanceName NVARCHAR(512), SubclientName NVARCHAR(128), 
	SPId INT, SPName NVARCHAR(144), UncompBytes BIGINT, DedupEnabled INT, SecondaryEncryption INT, bEnterprise INT
)

IF @SpVersion > 0 
BEGIN
	IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
	BEGIN
		ALTER TABLE #CapacityUsage DROP COLUMN SnapshotSize
		ALTER TABLE #CapacityUsage DROP COLUMN ReplicationSize
		ALTER TABLE #CapacityUsage DROP COLUMN InstanceName
	END
	ELSE
	IF @SpVersion <= 10031000800050000 --10.0 SP4 and Below
	BEGIN
		ALTER TABLE #CapacityUsage DROP COLUMN SnapshotSize
		ALTER TABLE #CapacityUsage DROP COLUMN ReplicationSize
	END
END
DECLARE @PeriodType VARCHAR(32) = CASE @Type WHEN 1 THEN 'ByLastMonth' WHEN 2 THEN 'ByLastWeek' WHEN 4 THEN 'ByLastDay' END

INSERT INTO #CapacityUsage
EXEC QS_CommCellUsageReport @PeriodType, 0, 1

UPDATE	#TblResults
SET		FrontEndSize = CU.UncompBytes, FrontEndSizeType = CU.nJobType
FROM	#TblResults R 
		INNER JOIN #CapacityUsage CU ON R.AppId = CU.appId AND CU.AppTypeId <> 106

DELETE	#CapacityUsage 
FROM	#TblResults R
WHERE	#CapacityUsage.appId = R.appId

INSERT INTO #TblResults
SELECT	@FromDateTime, ClientId, appId, 
		CASE WHEN defaultSnapCopy > 0 THEN defaultSnapCopy ELSE defaultCopy END,
		0, 0, 0, 0, UncompBytes, nJobType, 0, NULL
FROM	#CapacityUsage CU
		INNER JOIN archGroup AG WITH (NOLOCK) ON CU.SPId = AG.id AND CU.AppTypeId <> 106

DROP TABLE #CapacityUsage 


-- FrontEnd size of VMs

CREATE TABLE #VSAFullJob (vmClientId INT, jobId INT)

INSERT	INTO #VSAFullJob
SELECT	V.VMclientId, MAX(J.jobId)
FROM	APP_VMProp V WITH (NOLOCK)
		INNER JOIN JMBkpStats J WITH (NOLOCK)
	ON	V.jobId = J.jobId AND V.commCellId = J.commCellId AND V.attrName = 'vmStatus' AND V.attrVal = '0'
		AND J.commCellId = 2 AND J.appType = 106
		AND J.servEndDate <= @TimeRangeEnd
		AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
		AND J.status IN (1, 3, 14)
		AND J.bkpLevel IN (1, 64, 128, 1024, 16384, 32768) 
		AND J.fullCycleNum > 0 AND J.dataStatus = 0 AND J.totalUncompBytes > 0
GROUP BY V.VMclientId

-- vmGuestSize = Actual VM application size. vmUsedSpace = Data read during backup.
-- All jobs have vmUsedSpace before 10.0. All jobs have vmGuestSize since 10.0. Only Full jobs have vmUsedSpace since 10.0. 
;WITH VMFrontEndSize (VMclientId, lastJobId, frontendSize) AS
(	SELECT	P.VMclientId, MAX(jobId), MAX(ISNULL([vmGuestSize], [vmUsedSpace]))
	FROM	(SELECT	V.VMclientId, V.jobId, V.attrName, CAST(V.attrVal AS BIGINT) AS size
			FROM	#VSAFullJob S INNER JOIN APP_VMProp V WITH (NOLOCK) 
				ON V.VMclientId = S.VMClientId AND V.jobId >= S.jobId AND V.commCellId = 2
					AND V.attrName IN ('vmStatus', 'vmUsedSpace', 'vmGuestSize')) S
			PIVOT (MAX(size) FOR attrName IN ([vmStatus], [vmUsedSpace], [vmGuestSize])) AS P
	WHERE	P.vmStatus = 0
	GROUP BY P.VMclientId
)
, VMAppIdSize (VMclientId, appId, archGrpId, frontendSize, rowId) AS
(
	SELECT	S.VMClientId, J.appId, J.dataArchGrpId, S.frontendSize, ROW_NUMBER() OVER (PARTITION BY S.VMClientId ORDER BY J.servEndDate DESC)
	FROM	VMFrontEndSize S
			INNER JOIN APP_VMProp V WITH (NOLOCK) ON V.VMclientId = S.VMClientId AND V.attrName = 'vmStatus'
			INNER JOIN JMBkpStats J WITH (NOLOCK) ON V.jobId = J.jobId AND V.commCellId = J.commCellId
)
UPDATE	#TblResults
SET		FrontEndSize = V.frontendSize
FROM	#TblResults R 
	INNER JOIN VMAppIdSize V ON R.VMClientId = V.VMClientId
WHERE	rowId = 1

DROP TABLE #VSAFullJob


--------------------------------------------
-- End of FrontEndSize --
--------------------------------------------

SET_SURVEY_XML:

SELECT @LastCollectedPeriod = MAX(PeriodBegin) FROM #TblResults


SELECT	S.PeriodBegin AS '@StartDate',
	@Type AS '@Type',
	CASE WHEN S.VMClientId > 0 THEN S.VMClientId ELSE A.clientId END AS '@ClientId',
	S.appId AS '@AppId',
	S.CopyId AS '@CopyId',
	S.FrontEndSize AS '@FrontEndSize',
	S.FrontEndSizeType AS '@FrontEndSizeType',
	S.ApplicationSIze AS '@ApplicationSIze',
	ISNULL(S.BackupAppSize, S.DataProtected) AS '@BackupAppSize',
	S.DataProtected AS '@DataProtected',
	S.DataWritten AS '@DataWritten',
	S.TotalDataProtected AS '@TotalProtectedApp',
	S.TotalDataOnMedia AS '@TotalDataOnMedia'
FROM	#TblResults S
	INNER JOIN archGroupCopy C WITH (NOLOCK) ON S.CopyId = C.id
	INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
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 


--------- SET LastStorageUsageCollectedMonth ---------
IF @LastCollectedPeriod IS NOT NULL AND OBJECT_ID('SetSurveyParamValue') IS NULL
BEGIN
	IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @TimeStampName)
	BEGIN
		IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
			INSERT INTO GXGlobalParam (name, value) SELECT @TimeStampName, ''
		ELSE
		IF @OperationType = 1 
		BEGIN
			IF @Type = @CHARGEBACK_TYPE_MONTHLY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedMonth'', '''', 0, 0')
			ELSE IF @Type = @CHARGEBACK_TYPE_WEEKLY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedWeek'', '''', 0, 0')
			ELSE IF @Type = @CHARGEBACK_TYPE_DAILY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedDay'', '''', 0, 0')
		END
		ELSE IF @OperationType = 0 
		BEGIN
			IF @Type = @CHARGEBACK_TYPE_MONTHLY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedMonthPublic'', '''', 0, 0')
			ELSE IF @Type = @CHARGEBACK_TYPE_WEEKLY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedWeekPublic'', '''', 0, 0')
			ELSE IF @Type = @CHARGEBACK_TYPE_DAILY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedDayPublic'', '''', 0, 0')
		END
		ELSE IF @OperationType = 2
		BEGIN
			IF @Type = @CHARGEBACK_TYPE_MONTHLY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedMonthDirectDip'', '''', 0, 0')
			ELSE IF @Type = @CHARGEBACK_TYPE_WEEKLY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedWeekDirectDip'', '''', 0, 0')
			ELSE IF @Type = @CHARGEBACK_TYPE_DAILY
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''LastChargebackSizeCollectedDayDirectDip'', '''', 0, 0')
		END		
	END

END

FINISH:
IF OBJECT_ID('tempdb.dbo.#TblCopyIds') IS NOT NULL DROP TABLE #TblCopyIds
IF OBJECT_ID('tempdb.dbo.#TblResults') IS NOT NULL DROP TABLE #TblResults
IF OBJECT_ID('tempdb.dbo.#AppCopySize') IS NOT NULL DROP TABLE #AppCopySize
IF OBJECT_ID('tempdb.dbo.#DedupeCopy') IS NOT NULL DROP TABLE #DedupeCopy
IF OBJECT_ID('tempdb.dbo.#VMSize') IS NOT NULL DROP TABLE #VMSize
IF OBJECT_ID('tempdb.dbo.#VMJobSize') IS NOT NULL DROP TABLE #VMJobSize
IF OBJECT_ID('tempdb.dbo.#VMJobCopy') IS NOT NULL DROP TABLE #VMJobCopy
IF OBJECT_ID('tempdb.dbo.#SynthFullJobVM') IS NOT NULL DROP TABLE #SynthFullJobVM
IF OBJECT_ID('tempdb.dbo.#UsageDate') IS NOT NULL DROP TABLE #UsageDate

END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

IF (@ErrorCode = 0 AND @LastCollectedPeriod IS NOT NULL)
BEGIN
	IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
	BEGIN
		set @LastCollectedPeriodSTR = CONVERT(VARCHAR, @LastCollectedPeriod, 101)
		EXEC SetSurveyParamValue @TimeStampName, @LastCollectedPeriodSTR
	END
	ELSE
	BEGIN
		UPDATE	GXGlobalParam SET value = CONVERT(VARCHAR, @LastCollectedPeriod, 101)
		WHERE	name = @TimeStampName
	END
END

SET NOCOUNT OFF