

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Sp/RptChargebackDiff.sp] ---------- 

-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/CloudDB/Sp/RptChargebackDiff.sp,v $ $Id: RptChargebackDiff.sp,v 1.1.2.23.4.2 2021/06/28 21:05:16 junlu Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='RptChargebackDiff')
BEGIN
	print '>>> Drop Stored Procedure: RptChargebackDiff <<<'
	drop procedure RptChargebackDiff
END
IF EXISTS (select * from GxQscripts where name='RptChargebackDiff')
	delete from GxQscripts where name = 'RptChargebackDiff'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptChargebackDiff')
	delete from GXDBVersions where aliasname = 'RptChargebackDiff'
GO
print '... Creating Procedure: RptChargebackDiff'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptChargebackDiff
  @i_UserId INT = NULL,
  @i_GroupId INT = NULL,
  @i_CommServUniqueID INT = 0,
  @i_IsGraph INT = 0,
  @i_TopN INT = -1, -- 0 for No Aggregate, -1 for Total Aggregate
  @i_GroupBy VARCHAR(64) = 'Subclient', -- 'Subclient', 'Client', 'Agent Type', 'Storage Policy', 'Storage Policy Copy' or 'CommCell' or 'Tenant'
  @i_GroupByID INT = 0, -- For Entity Selection
  @i_EntityType VARCHAR(64) = '', -- 'Subclient', 'Agent Type', 'Storage Policy', 'Storage Policy Copy' or 'CommCell' or 'Tenant'
  @i_EntityID INT = 0, -- NULL for trending chart of total
  @i_GrowthType INT = 0, -- 0/1 for Frontend Backup/Archive Size, 2 for Primary App Size per interval, 3 for All Media Size, 4 for Media Size (written in the month), 5 for All App Size
  @i_IntervalType INT = 3, -- 1 for Daily, 2 for Weekly, 3 for Monthly
  @i_FirstDate DATE = NULL,
  @i_SecondDate DATE = NULL,
  @i_Options INT = 0 --1 for Exclude Deconfigured Subclients, 2 for Show FET for VSA and Other iDAs, 16 for Include DR/DDB/Index subclients, 64 for Primary Copy Only
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ChargebackType INT = 1
DECLARE @i_QueryId INT = 136 -- Monthly
IF @i_IntervalType = 2
BEGIN
	SET @ChargebackType = 2
	SET @i_QueryId = 162 -- Weekly
END
ELSE
IF @i_IntervalType = 1
BEGIN
	SET @ChargebackType = 4
	SET @i_QueryId = 72  -- Daily
END
IF EXISTS (SELECT 1 FROM cf_SurveyConfig WITH (NOLOCK) WHERE Name = 'Chargeback_Include_DR_DDB_Index_Subclients' AND Value = '1')
    SET @i_Options |= 16
IF EXISTS (SELECT 1 FROM cf_SurveyConfig WHERE Name = 'Chargeback_Exclude_Backup_Disabled_Subclients' AND Value = '1')
    SET @i_Options |= 32
IF EXISTS (SELECT 1 FROM cf_SurveyConfig WHERE Name = 'Chargeback_Primary_Copy_Only' AND Value = '1')
    SET @i_Options |= 64
DECLARE @LastDate  DATE
DECLARE @TotalAggregate INT = 0
IF @i_TopN = -1 SET @TotalAggregate = 1
IF @i_TopN <= 0 SET @i_TopN = 0x7FFFFFFF
CREATE TABLE #CommServ (CommservUniqueId INT, CommCellID VARCHAR(32), CommServName NVARCHAR(288))
INSERT	INTO #CommServ
SELECT	DISTINCT CommservUniqueId,
 		CASE WHEN @i_CommServUniqueID > 0 THEN '' WHEN CommCellID = -1 THEN ' - FFFFFFFF' ELSE ' - ' + dbo.ConvertBigIntToHexStr(CommCellID) END,
 		DisplayName + CASE WHEN @i_CommServUniqueID > 0 THEN '' WHEN CommCellID = -1 THEN ' - FFFFFFFF' ELSE ' - ' + dbo.ConvertBigIntToHexStr(CommCellID) END
FROM	dbo.cf_SurveyResultFuncV1(@i_QueryId, @i_CommServUniqueID, 1, NULL, NULL, @i_UserId, @i_GroupId)
CREATE TABLE #Results (CommservUniqueId INT, StartDate DATE, SurveyResultId BIGINT)
INSERT	INTO #Results
SELECT	CB.CommservUniqueId, StartDate, MAX(SurveyResultId) AS SurveyResultId
FROM	cf_SurveyMonthlyChargeback CB WITH (NOLOCK)
		INNER JOIN #CommServ CS ON CB.CommservUniqueId = CS.CommservUniqueId
WHERE	Type = @ChargebackType AND StartDate IN (@i_FirstDate, @i_SecondDate)
GROUP BY CB.CommservUniqueId, StartDate
CREATE TABLE #ClientGroup (CommServUniqueId INT, ClientGroupId INT, ClientGroupName NVARCHAR(256), TenantId INT, TenantGUID UNIQUEIDENTIFIER, TenantName NVARCHAR(256))
IF @i_GroupBy = 'Tenant' OR @i_EntityType = 'Tenant'
BEGIN
	INSERT INTO #ClientGroup
	SELECT	DISTINCT CG.CommservUniqueId, CG.ClientGroupId, CG.ClientGroupName, T.TenantID, T.TenantGUID, T.TenantName
	FROM	cf_SurveyClientGroup CG WITH (NOLOCK)
			INNER JOIN cf_SurveyTenants T WITH (NOLOCK) ON CG.CommservUniqueId = T.CommservUniqueId AND CG.ClientGroupId = T.ClientGroupId
			INNER JOIN #CommServ CS ON CG.CommservUniqueId = CS.CommservUniqueId
	WHERE	T.DeletedDate IS NULL
END
CREATE TABLE #FrontEndCopy (CommServUniqueId INT, SurveyResultId BIGINT, ClientId INT, AppTypeId INT, AppId INT, CopyId INT, IsDefaultCopy INT, IsSnapCopy INT, IsFrontEndCopy INT, PolicyId INT, PrimaryCopyId INT, FrontEndSize BIGINT)
IF @i_Options&64 > 0
	INSERT INTO #FrontEndCopy
	SELECT	T.CommServUniqueId, T.SurveyResultId, T.ClientId, 106, T.AppId, T.CopyId,
			T.IsDefaultCopy, T.IsSnapCopy, CASE WHEN RowNum = 1 THEN 1 ELSE 0 END,
			T.PolicyId, T.CopyId, FrontEndSize
	FROM	(SELECT	U.CommServUniqueId, U.SurveyResultId, U.ClientId, U.AppId, U.CopyId, C.IsDefaultCopy, C.IsSnapCopy, C.PolicyId, U.FrontEndSize,
					ROW_NUMBER() OVER (PARTITION BY U.SurveyResultId, U.ClientId, U.AppId ORDER BY C.IsDefaultCopy DESC, U.FrontEndSize DESC) AS RowNum
			FROM	cf_SurveyMonthlyChargeback U WITH (NOLOCK)
				INNER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK) ON U.CommservUniqueId = C.CommservUniqueId AND U.CopyId = C.CopyId AND C.IsDefaultCopy > 0
				INNER JOIN #Results R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate) T
ELSE
	INSERT INTO #FrontEndCopy
	SELECT	T.CommServUniqueId, T.SurveyResultId, T.ClientId, 106, T.AppId, T.CopyId,
			T.IsDefaultCopy, T.IsSnapCopy, CASE WHEN RowNum = 1 THEN 1 ELSE 0 END,
			T.PolicyId, CASE WHEN T.IsDefaultCopy > 0 THEN T.CopyId ELSE 0 END, FrontEndSize
	FROM	(SELECT	U.CommServUniqueId, U.SurveyResultId, U.ClientId, U.AppId, U.CopyId, C.IsDefaultCopy, C.IsSnapCopy, C.PolicyId, U.FrontEndSize,
					ROW_NUMBER() OVER (PARTITION BY U.SurveyResultId, U.ClientId, U.AppId ORDER BY C.IsDefaultCopy DESC, U.FrontEndSize DESC) AS RowNum
			FROM	cf_SurveyMonthlyChargeback U WITH (NOLOCK)
				INNER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK) ON U.CommservUniqueId = C.CommservUniqueId AND U.CopyId = C.CopyId
				INNER JOIN #Results R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate) T
UPDATE	T SET appTypeId = A.appTypeId
FROM	#FrontEndCopy T
		INNER JOIN cf_SurveySubclientAssociation A WITH (NOLOCK)
	 ON T.CommServUniqueId = A.CommServUniqueId AND T.ClientId = A.ClientId AND T.AppId = A.AppId AND A.AppId > 0 AND A.AppTypeId <> 106
IF EXISTS (SELECT ClientGroupId FROM #ClientGroup)
	DELETE	FC
	FROM	#FrontEndCopy FC
			LEFT OUTER JOIN (
				SELECT	DISTINCT CGA.CommservUniqueId, CGA.ClientId
				FROM	cf_SurveyClientGroupAssociation CGA WITH (NOLOCK)
					INNER JOIN #ClientGroup CG ON CGA.CommservUniqueId = CG.CommservUniqueId AND CGA.ClientGroupId = CG.ClientGroupId
				) CL ON FC.CommservUniqueId = CL.CommservUniqueId AND FC.ClientId = CL.ClientId
	WHERE	CL.ClientId IS NULL
CREATE INDEX FrontEndCopy_idx1 ON #FrontEndCopy (SurveyResultId, ClientId, AppId, CopyId, IsFrontEndCopy) INCLUDE (AppTypeId)
CREATE INDEX FrontEndCopy_idx2 ON #FrontEndCopy (CommservUniqueId, ClientId, AppId, CopyId) INCLUDE (AppTypeId)
-- Exclude VSA subclients since data sizes are counted for IndexingV2 VMs
DELETE	T
FROM	#FrontEndCopy T
		INNER JOIN (
			SELECT	DISTINCT C.CommServUniqueId, C.ClientId, C.AppId
			FROM	#FrontEndCopy C
					INNER JOIN cf_SurveySubclientAssociation A WITH (NOLOCK)
						ON C.CommServUniqueId = A.CommServUniqueId AND C.ClientId = A.ClientId AND C.AppId = A.AppId
							AND A.AppTypeId = 106 AND A.InstanceName = 'VMInstance'
			) S ON T.CommServUniqueId = S.CommServUniqueId AND T.ClientId = S.ClientId AND T.AppTypeId = 106 AND T.AppId <> S.AppId
-- Exclude VSA subclients since data sizes are counted for each VMs
DELETE	#FrontEndCopy
FROM	#FrontEndCopy C
		INNER JOIN cf_SurveySubclientAssociation A WITH (NOLOCK)
		ON C.CommServUniqueId = A.CommservUniqueId AND C.ClientId = A.ClientId AND C.AppId = A.AppId
			AND A.AppId > 0 AND A.AppTypeId = 106 AND A.InstanceName <> 'VMInstance'
-- Exclude Deconfigured Clients/Subclients
IF	(@i_Options & 1) = 1
BEGIN
	DELETE	S
	FROM	#FrontEndCopy S INNER JOIN cf_SurveySubclientAssociation A
			ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId AND A.AppId > 0
	WHERE	(A.SubclientStatus & (2|4)) > 0 OR A.Deleted = 1
	DELETE	S
	FROM	#FrontEndCopy S INNER JOIN cf_SurveyClient CL
			ON S.CommServUniqueId = CL.CommServUniqueId AND S.ClientId = CL.ClientId
	WHERE	CL.Deleted = 1
END
-- Exclude DR/DDB/Index subclients
IF	(@i_Options & 16) = 0
	DELETE	S
	FROM	#FrontEndCopy S INNER JOIN cf_SurveySubclientAssociation A
			ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId AND A.AppId > 0
	WHERE	(A.SpecialTypeFlags & (2|4|8)) > 0 OR A.AppTypeId = 1000
-- Exclude Backup Activity Disabled Subclients
IF	(@i_Options & 32) = 32
	DELETE	S
	FROM	#FrontEndCopy S INNER JOIN cf_SurveySubclientAssociation A
			ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId AND A.AppId > 0
	WHERE	A.BackupDisabled = 1
-- Take VSA backup for frontend size if other agents are installed on VM
IF @i_Options&2 = 0
	UPDATE	C SET IsFrontEndCopy = 0
	FROM	#FrontEndCopy C
			INNER JOIN (
				SELECT	DISTINCT SurveyResultId, ClientId
				FROM	#FrontEndCopy
				WHERE	AppTypeId = 106 AND IsFrontEndCopy = 1 AND FrontEndSize > 0
			) A ON C.SurveyResultId = A.SurveyResultId AND C.ClientId = A.ClientId
	WHERE	C.AppTypeId <> 106 AND C.IsFrontEndCopy = 1
UPDATE	#FrontEndCopy SET PrimaryCopyId = C.CopyId
FROM	cf_SurveyCopyBasicRetention C WITH (NOLOCK)
WHERE	#FrontEndCopy.CommServUniqueId = C.CommservUniqueId AND #FrontEndCopy.PolicyId = C.PolicyId
	AND #FrontEndCopy.IsSnapCopy = C.IsSnapCopy AND C.IsDefaultCopy = 1 AND #FrontEndCopy.PrimaryCopyId = 0
-- VMs in Multi-subclients
CREATE TABLE #MultiSubclientVM (SurveyResultId BIGINT, ClientId INT)
INSERT	INTO #MultiSubclientVM
SELECT	C.SurveyResultId, C.ClientId
FROM	#FrontEndCopy C
		INNER JOIN cf_SurveySubclientAssociation A WITH (NOLOCK)
		ON C.CommServUniqueId = A.CommservUniqueId AND C.ClientId = A.ClientId
			AND A.AppId = 0 AND A.AppTypeId = 106 AND C.AppTypeId = 106 AND C.IsFrontEndCopy = 1 AND C.FrontEndSize > 0
GROUP BY C.SurveyResultId, C.ClientId
HAVING COUNT(C.ClientId) > 1
IF EXISTS (SELECT * FROM #MultiSubclientVM)
BEGIN
	UPDATE	C SET IsFrontEndCopy = 0
	FROM	#FrontEndCopy C
			INNER JOIN (
			SELECT	F.SurveyResultId, F.ClientId, F.AppId, F.CopyId, ROW_NUMBER() OVER (PARTITION BY F.SurveyResultId, F.ClientId ORDER BY F.FrontEndSize DESC) AS RowNum
			FROM	#FrontEndCopy F
					INNER JOIN #MultiSubclientVM V ON F.SurveyResultId = V.SurveyResultId AND F.ClientId = V.ClientId
						AND F.AppTypeId = 106 AND F.IsFrontEndCopy = 1
			) S ON C.SurveyResultId = S.SurveyResultId AND C.ClientId = S.ClientId AND C.AppId = S.AppId AND C.CopyId = S.CopyId
	WHERE	RowNum > 1
END
DROP TABLE #MultiSubclientVM
CREATE TABLE #ChargebackSize (
		CommservUniqueId INT, ClientId INT, AppTypeId INT, AppId INT, CopyId INT, StartDate DATE, DataSizeMB BIGINT)
IF @i_GrowthType IN (0, 1)
	INSERT INTO #ChargebackSize
	SELECT	S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate, SUM(S.FrontEndSize)/1024/1024
	FROM	cf_SurveyMonthlyChargeback S WITH (NOLOCK)
		INNER JOIN #FrontEndCopy C ON S.SurveyResultId = C.SurveyResultId AND S.ClientId = C.ClientId AND S.AppId = C.AppId AND S.CopyId = C.CopyId
	WHERE	IsFrontEndCopy = 1 AND FrontendSizeType = @i_GrowthType
	GROUP BY S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate
ELSE
IF @i_GrowthType = 2
	INSERT INTO #ChargebackSize
	SELECT	S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate, SUM(S.DataProtected)/1024/1024
	FROM	cf_SurveyMonthlyChargeback S WITH (NOLOCK)
		INNER JOIN #FrontEndCopy C ON S.SurveyResultId = C.SurveyResultId AND S.ClientId = C.ClientId AND S.AppId = C.AppId AND S.CopyId = C.CopyId
	WHERE	S.CopyId = C.PrimaryCopyId
	GROUP BY S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate
ELSE
IF @i_GrowthType = 3
	INSERT INTO #ChargebackSize
	SELECT	S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate, SUM(S.TotalDataOnMedia)/1024/1024
	FROM	cf_SurveyMonthlyChargeback S WITH (NOLOCK)
		INNER JOIN #FrontEndCopy C ON S.SurveyResultId = C.SurveyResultId AND S.ClientId = C.ClientId AND S.AppId = C.AppId AND S.CopyId = C.CopyId
	GROUP BY S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate
ELSE
IF @i_GrowthType = 4
	INSERT INTO #ChargebackSize
	SELECT	S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate, SUM(S.DataWritten)/1024/1024
	FROM	cf_SurveyMonthlyChargeback S WITH (NOLOCK)
		INNER JOIN #FrontEndCopy C ON S.SurveyResultId = C.SurveyResultId AND S.ClientId = C.ClientId AND S.AppId = C.AppId AND S.CopyId = C.CopyId
	GROUP BY S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate
ELSE
IF @i_GrowthType = 5
	INSERT INTO #ChargebackSize
	SELECT	S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate, SUM(S.TotalProtectedApp)/1024/1024
	FROM	cf_SurveyMonthlyChargeback S WITH (NOLOCK)
		INNER JOIN #FrontEndCopy C ON S.SurveyResultId = C.SurveyResultId AND S.ClientId = C.ClientId AND S.AppId = C.AppId AND S.CopyId = C.CopyId
	GROUP BY S.CommservUniqueId, S.ClientId, C.AppTypeId, S.AppId, S.CopyId, S.StartDate
IF @i_GroupBy = 'Subclient' AND @i_CommServUniqueID > 0 AND @i_GroupByID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR AppId <> @i_GroupByID
ELSE
IF @i_GroupBy = 'Client' AND @i_CommServUniqueID > 0 AND @i_GroupByID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR ClientId <> @i_GroupByID
ELSE
IF @i_GroupBy = 'Agent Type' AND @i_GroupByID > 0
	DELETE	#ChargebackSize
	WHERE	AppTypeId <> @i_GroupByID
ELSE
IF @i_GroupBy = 'Storage Policy' AND @i_CommServUniqueID > 0 AND @i_GroupByID > 0
	DELETE	D FROM #ChargebackSize D LEFT OUTER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK)
		 ON C.CommservUniqueId = @i_CommServUniqueID AND C.PolicyId = @i_GroupByID AND D.CopyId = C.CopyId
	WHERE	D.CommservUniqueId <> @i_CommServUniqueID OR C.CopyId IS NULL
ELSE
IF @i_GroupBy = 'Storage Policy Copy' AND @i_CommServUniqueID > 0 AND @i_GroupByID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR CopyId <> @i_GroupByID
ELSE
IF @i_GroupBy = 'CommCell' AND @i_GroupByID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_GroupByID
ELSE
IF @i_GroupBy = 'Tenant' AND @i_GroupByID > 0
BEGIN
	DELETE	#ClientGroup
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR TenantId <> @i_GroupByID
	DELETE	T
	FROM	#ChargebackSize T
			LEFT OUTER JOIN (
			SELECT	DISTINCT CGA.CommservUniqueId, CGA.ClientId
			FROM	cf_SurveyClientGroupAssociation CGA WITH (NOLOCK)
					INNER JOIN #ClientGroup CG ON CGA.CommservUniqueId = CG.CommservUniqueId AND CGA.ClientGroupId = CG.ClientGroupId
			WHERE	CG.TenantId = @i_GroupByID
			) S ON T.CommservUniqueId = S.CommservUniqueId AND T.ClientId = S.ClientId
	WHERE	S.ClientId IS NULL
END
IF @i_EntityType = 'Subclient' AND @i_CommServUniqueID > 0 AND @i_EntityID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR AppId <> @i_EntityID
ELSE
IF @i_EntityType = 'Client' AND @i_CommServUniqueID > 0 AND @i_EntityID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR ClientId <> @i_EntityID
ELSE
IF @i_EntityType = 'Agent Type' AND @i_EntityID > 0
	DELETE	#ChargebackSize
	WHERE	AppTypeId <> @i_EntityID
ELSE
IF @i_EntityType = 'Storage Policy' AND @i_CommServUniqueID > 0 AND @i_EntityID > 0
	DELETE	D FROM #ChargebackSize D LEFT OUTER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK)
		 ON C.CommservUniqueId = @i_CommServUniqueID AND D.CopyId = C.CopyId AND C.PolicyId = @i_EntityID
	WHERE	D.CommservUniqueId <> @i_CommServUniqueID OR C.CopyId IS NULL
ELSE
IF @i_EntityType = 'Storage Policy Copy' AND @i_CommServUniqueID > 0 AND @i_EntityID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR CopyId <> @i_EntityID
ELSE
IF @i_EntityType = 'CommCell' AND @i_EntityID > 0
	DELETE	#ChargebackSize
	WHERE	CommservUniqueId <> @i_EntityID
ELSE
IF @i_EntityType = 'Tenant' AND @i_EntityID > 0
BEGIN
	DELETE	#ClientGroup
	WHERE	CommservUniqueId <> @i_CommServUniqueID OR TenantId <> @i_EntityID
	DELETE	T
	FROM	#ChargebackSize T
			LEFT OUTER JOIN (
			SELECT	DISTINCT CGA.CommservUniqueId, CGA.ClientId
			FROM	cf_SurveyClientGroupAssociation CGA WITH (NOLOCK)
					INNER JOIN #ClientGroup CG ON CGA.CommservUniqueId = CG.CommservUniqueId AND CGA.ClientGroupId = CG.ClientGroupId
			WHERE	CG.TenantId = @i_EntityID
			) S ON T.CommservUniqueId = S.CommservUniqueId AND T.ClientId = S.ClientId
	WHERE	S.ClientId IS NULL
END
CREATE TABLE #EntityTrend (CommservUniqueId INT, EntityId INT, EntityName NVARCHAR(1024), EntityGUID UNIQUEIDENTIFIER, StartDate DATE, DataSizeMB BIGINT)
CREATE TABLE #EntityDiff  (CommservUniqueId INT, EntityId INT, EntityName NVARCHAR(1024), EntityGUID UNIQUEIDENTIFIER, FirstSizeMB BIGINT, SecondSizeMB BIGINT, SizeDiffMB BIGINT)
IF @i_GroupBy = 'CommCell'
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	D.CommservUniqueId, D.CommservUniqueId, CommServName, NULL, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D
		INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
	GROUP BY D.CommservUniqueId, D.CommservUniqueId, CommServName, StartDate
END
ELSE
IF @i_GroupBy = 'Agent Type' AND @i_CommServUniqueID = 0
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	0, AppTypeId, A.name, NULL, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D INNER JOIN cf_SurveyAgentType A WITH (NOLOCK)
		 ON D.AppTypeId = A.id
	GROUP BY AppTypeId, A.name, StartDate
END
ELSE
IF @i_GroupBy = 'Agent Type' AND @i_CommServUniqueID > 0
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	D.CommservUniqueId, AppTypeId, A.name, NULL, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D INNER JOIN cf_SurveyAgentType A WITH (NOLOCK)
		 ON D.AppTypeId = A.id
	GROUP BY D.CommservUniqueId, AppTypeId, A.name, StartDate
END
ELSE
IF @i_GroupBy = 'Storage Policy'
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	D.CommservUniqueId, PolicyId, PolicyName, NULL, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D INNER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK)
		 ON D.CommservUniqueId = C.CommservUniqueId AND D.CopyId = C.CopyId
	GROUP BY D.CommservUniqueId, PolicyId, PolicyName, StartDate
END
ELSE
IF @i_GroupBy = 'Storage Policy Copy'
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	D.CommservUniqueId, D.CopyId, PolicyName+':'+CopyName, NULL, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D INNER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK)
		 ON D.CommservUniqueId = C.CommservUniqueId AND D.CopyId = C.CopyId
	GROUP BY D.CommservUniqueId, D.CopyId, PolicyName+':'+CopyName, StartDate
END
ELSE
IF @i_GroupBy = 'Client'
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	D.CommservUniqueId, D.ClientId, ClientName, NULL, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D
		INNER JOIN (
			SELECT	DISTINCT A.CommservUniqueId, A.ClientId, A.ClientName
			FROM	cf_SurveySubclientAssociation A WITH (NOLOCK)
				INNER JOIN #CommServ R ON A.CommservUniqueId = R.CommservUniqueId
			) S ON D.CommservUniqueId = S.CommservUniqueId AND D.ClientId = S.ClientId
	GROUP BY D.CommservUniqueId, D.ClientId, ClientName, StartDate
END
ELSE
IF @i_GroupBy = 'Subclient'
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	D.CommservUniqueId, D.AppId, ClientName+':'+AgentName+':'+InstanceName+':'+BackupsetName+':'+SubclientName, NULL, StartDate,
			SUM(DataSizeMB)
	FROM	#ChargebackSize D INNER JOIN cf_SurveySubclientAssociation S WITH (NOLOCK)
		 ON D.CommservUniqueId = S.CommservUniqueId AND D.ClientId = S.ClientId AND D.AppId = S.AppId AND D.AppTypeId <> 106
	GROUP BY D.CommservUniqueId, D.AppId, ClientName+':'+AgentName+':'+InstanceName+':'+BackupsetName+':'+SubclientName, StartDate
END
ELSE
IF @i_GroupBy = 'Tenant'
BEGIN
	INSERT	INTO #EntityTrend
	SELECT	S.CommservUniqueId, S.TenantId, S.TenantName, S.TenantGUID, StartDate, SUM(DataSizeMB)
	FROM	#ChargebackSize D
		INNER JOIN (
			SELECT	DISTINCT CGA.CommservUniqueId, CGA.ClientId, CG.TenantId, CG.TenantGUID, CG.TenantName
			FROM	cf_SurveyClientGroupAssociation CGA WITH (NOLOCK)
					INNER JOIN #ClientGroup CG ON CGA.CommservUniqueId = CG.CommservUniqueId AND CGA.ClientGroupId = CG.ClientGroupId
			) S ON D.CommservUniqueId = S.CommservUniqueId AND D.ClientId = S.ClientId
	GROUP BY S.CommservUniqueId, S.TenantId, S.TenantGUID, S.TenantName, StartDate
END
DROP TABLE #ChargebackSize
CREATE CLUSTERED INDEX #EntityTrend_idx ON #EntityTrend (CommservUniqueId, EntityId, FirstDate)
INSERT	INTO #EntityDiff
SELECT	TOP (@i_TopN) ISNULL(T2.CommservUniqueId, T1.CommservUniqueId), ISNULL(T2.EntityId, T1.EntityId), ISNULL(T2.EntityName, T1.EntityName), ISNULL(T2.EntityGUID, T1.EntityGUID),
		ISNULL(T1.DataSizeMB,0), ISNULL(T2.DataSizeMB,0), (ISNULL(T2.DataSizeMB,0) - ISNULL(T1.DataSizeMB,0)) AS SizeDiffMB
FROM	(SELECT * FROM #EntityTrend WHERE StartDate = @i_FirstDate) T1
		FULL OUTER JOIN
		(SELECT * FROM #EntityTrend WHERE StartDate = @i_SecondDate) T2
	 ON	T1.CommservUniqueId = T2.CommservUniqueId AND T1.EntityId = T2.EntityId
ORDER BY SizeDiffMB DESC
CREATE CLUSTERED INDEX #EntityDiff_idx ON #EntityDiff (CommservUniqueId, EntityId)
IF  @i_CommServUniqueID = 0 AND @i_IsGraph = 1 AND @i_GroupBy IN ('Storage Policy', 'Storage Policy Copy', 'Subclient', 'Client')
	UPDATE	#EntityDiff SET EntityName += (' - ' + CommCellID)
	FROM	#EntityDiff D
			INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
DROP TABLE #EntityTrend
IF @i_IsGraph > 0
BEGIN
	IF @TotalAggregate = 1
		SELECT	SUM(FirstSizeMB) AS FirstSizeMB, SUM(SecondSizeMB) AS SecondSizeMB, SUM(SizeDiffMB) AS SizeDiffMB
		FROM	#EntityDiff
	ELSE
		SELECT	EntityName, FirstSizeMB, SecondSizeMB, SizeDiffMB
		FROM	#EntityDiff
		ORDER BY SizeDiffMB DESC, EntityName
END
ELSE
BEGIN
	IF @i_GroupBy = 'Agent Type' AND @i_CommServUniqueID = 0
		SELECT	EntityId AS [EntityId<DONOTSHOW>], EntityName AS [Entity Name],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>]
		FROM	#EntityDiff
		ORDER BY SizeDiffMB DESC, EntityName
	ELSE
	IF @i_GroupBy = 'Agent Type' AND @i_CommServUniqueID > 0
		SELECT	R.CommservUniqueId AS [CommservUniqueId<DONOTSHOW>], R.CommServName AS [CommCell Name],
				EntityId AS [EntityId<DONOTSHOW>], EntityName AS [Entity Name],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>]
		FROM	#EntityDiff D
				INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
		ORDER BY SizeDiffMB DESC, EntityName
	ELSE
	IF @i_GroupBy = 'CommCell'
		SELECT	R.CommservUniqueId AS [CommservUniqueId<DONOTSHOW>], R.CommServName AS [CommCell Name],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>]
		FROM	#EntityDiff D
				INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
		ORDER BY SizeDiffMB DESC, CommServName
	ELSE
	IF @i_GroupBy = 'Tenant'
		SELECT	R.CommservUniqueId AS [CommservUniqueId<DONOTSHOW>], R.CommServName AS [CommCell Name],
				EntityId AS [EntityId<DONOTSHOW>], EntityName AS [Tenant Name], EntityGUID AS [Tenant GUID<HIDDEN>],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>],
				CASE WHEN ISNULL(installdate,'1970-01-01') > '1970-01-01' THEN DATEDIFF(SS, '01/01/1970', installdate) ELSE -50
					END AS 'Client Install Date<MMMDDYYYY><HIDDEN>',
				CASE WHEN InstallStatus > 0 AND ISNULL(UninstallTime,'1970-01-01') > ISNULL(installdate,'1970-01-01')
					THEN DATEDIFF(SS, '01/01/1970', CAST(UninstallTime AS DATE)) ELSE -50
					END AS 'Client Deconfigure Date<MMMDDYYYY><HIDDEN>',
				CASE WHEN InstallStatus = 0 THEN 'Installed' WHEN InstallStatus IN (1,2) THEN 'Deconfigured' ELSE 'N/A'
					END AS 'Client Status<HIDDEN>'
		FROM	#EntityDiff D
				INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
				LEFT OUTER JOIN cf_SurveyClient CL ON D.CommservUniqueId = CL.CommservUniqueId AND D.EntityId = CL.ClientId
		ORDER BY SizeDiffMB DESC, EntityName
	ELSE
	IF @i_GroupBy IN ('Client', 'Subclient')
		SELECT	R.CommservUniqueId AS [CommservUniqueId<DONOTSHOW>], R.CommServName AS [CommCell Name],
				EntityId AS [EntityId<DONOTSHOW>], EntityName AS [Entity Name],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>],
				CASE WHEN ISNULL(installdate,'1970-01-01') > '1970-01-01' THEN DATEDIFF(SS, '01/01/1970', installdate) ELSE -50
					END AS 'Client Install Date<MMMDDYYYY><HIDDEN>',
				CASE WHEN InstallStatus > 0 AND ISNULL(UninstallTime,'1970-01-01') > ISNULL(installdate,'1970-01-01')
					THEN DATEDIFF(SS, '01/01/1970', CAST(UninstallTime AS DATE)) ELSE -50
					END AS 'Client Deconfigure Date<MMMDDYYYY><HIDDEN>',
				CASE WHEN InstallStatus = 0 THEN 'Installed' WHEN InstallStatus IN (1,2) THEN 'Deconfigured' ELSE 'N/A'
					END AS 'Client Status<HIDDEN>'
		FROM	#EntityDiff D
				INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
				LEFT OUTER JOIN cf_SurveyClient CL ON D.CommservUniqueId = CL.CommservUniqueId AND D.EntityId = CL.ClientId
		ORDER BY SizeDiffMB DESC, EntityName
	ELSE
	IF @i_GroupBy = 'Storage Policy'
		SELECT	R.CommservUniqueId AS [CommservUniqueId<DONOTSHOW>], R.CommServName AS [CommCell Name],
				EntityId AS [EntityId<DONOTSHOW>], EntityName AS [Entity Name],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>],
				C.RetentionDays AS [Primary Retention Days<HIDDEN>], C.RetentionCycles AS [Primary Retention Cycles<HIDDEN>],
				CASE WHEN C.DedupeFlags > 0 THEN 'Yes' ELSE 'No' END AS [Dedupe Primary Copy<HIDDEN>],
				ISNULL(C.DefaultLibrary, '') AS [Library Name<HIDDEN>]
		FROM	#EntityDiff D
				INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
				INNER JOIN (SELECT DISTINCT CommservUniqueId, PolicyId, PrimaryCopyId FROM #FrontEndCopy WHERE IsSnapCopy = 0) P
					ON D.CommservUniqueId = P.CommservUniqueId AND D.EntityId = P.PolicyId
				LEFT OUTER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK)
					ON P.CommservUniqueId = C.CommservUniqueId AND P.PolicyId = C.PolicyId AND P.PrimaryCopyId = C.CopyId
		ORDER BY SizeDiffMB DESC, EntityName
	ELSE
	IF @i_GroupBy = 'Storage Policy Copy'
		SELECT	R.CommservUniqueId AS [CommservUniqueId<DONOTSHOW>], R.CommServName AS [CommCell Name],
				EntityId AS [EntityId<DONOTSHOW>], EntityName AS [Entity Name],
				FirstSizeMB AS [First Size<SIZEMB>], SecondSizeMB AS [Second Size<SIZEMB>], SizeDiffMB AS [Size Difference<DifferenceMB>],
				CASE WHEN FirstSizeMB > 0 THEN CAST(100.0*SizeDiffMB/FirstSizeMB AS DECIMAL(30, 2)) ELSE 0 END AS [Size Difference (%)<DifferenceNA>],
				C.RetentionDays AS [Retention Days<HIDDEN>], C.RetentionCycles AS [Retention Cycles<HIDDEN>],
				CASE WHEN C.DedupeFlags > 0 THEN 'Yes' ELSE 'No' END AS [Dedupe Copy<HIDDEN>],
				ISNULL(C.DefaultLibrary, '') AS [Library Name<HIDDEN>]
		FROM	#EntityDiff D
				INNER JOIN #CommServ R ON D.CommservUniqueId = R.CommservUniqueId
				LEFT OUTER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK) ON D.CommservUniqueId = C.CommservUniqueId AND D.EntityId = C.CopyId
		ORDER BY SizeDiffMB DESC, EntityName
END
DROP TABLE #CommServ
DROP TABLE #Results
DROP TABLE #ClientGroup
DROP TABLE #EntityDiff
DROP TABLE #FrontEndCopy
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'RptChargebackDiff')
	delete from GxQscripts where name = 'RptChargebackDiff'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptChargebackDiff')
	delete from GXDBVersions where aliasname = 'RptChargebackDiff'
GO

insert into GXDBVersions values(2, 'RptChargebackDiff',  'v1.1.2.23.4.2', 'RptChargebackDiff', 'v1.1.2.23.4.2')
GO

