

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

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


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

IF EXISTS (select * from GXDBVersions where aliasname='RptMonthlyStorageUsage')
	delete from GXDBVersions where aliasname = 'RptMonthlyStorageUsage'
GO
print '... Creating Procedure: RptMonthlyStorageUsage'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptMonthlyStorageUsage
  @i_UserId INT = NULL,
  @i_GroupId INT = NULL,
  @i_AggregateLevel INT = 0, --0 for Subclient, 1 for Client, 2 for CommCell, 3 for Client Group, 4 for Tag, 5 for Subclient per Copy, 6 for Tenant
  @i_CommServUniqueID INT = 0,
  @i_IncludeCost INT = 0,
  @i_StartDate DATETIME = NULL,
  @i_SizeUnit VARCHAR(8) = NULL,
  @i_ClientGroupName NVARCHAR(512) = NULL,
  @i_Type INT = 1,
  @i_Options INT = 0, --1 for Exclude Deconfigured Subclients, 2 for Show FET for VSA and Other iDAs, 4 for Show a row of Total, 8 for Show FET for each copy, 
                                     --16 for Include DR/DDB/Index subclients, 32 for Exclude Backup Activity Disabled subclients, 64 for Primary Copy Only
  @i_TagName NVARCHAR(256) = '',
  @i_TenantName NVARCHAR(256) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @i_QueryId INT = 136 -- Monthly
IF EXISTS (SELECT 1 FROM cf_SurveyConfig WITH (NOLOCK) WHERE Name = 'Chargeback_FET_ForEachCopy' AND Value = '1')
    SET @i_Options |= 8
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
IF @i_SizeUnit IS NULL
	SET @i_SizeUnit = 'TB'
ELSE
IF @i_SizeUnit NOT IN ('GB', 'TB', 'PB')
	SET @i_SizeUnit = 'TB'
DECLARE @BytesToTB   FLOAT = CAST(1 AS FLOAT)/1024/1024/1024/1024
DECLARE @BytesToUnit FLOAT = @BytesToTB
IF @i_SizeUnit = 'GB'
	SET @BytesToUnit = @BytesToUnit*1024.0
ELSE
IF @i_SizeUnit = 'PB'
	SET @BytesToUnit = @BytesToUnit/1024.0
IF @i_StartDate IS NULL
BEGIN
	SELECT	DISTINCT CASE WHEN Type = 1 AND DAY(StartDate) > 1 THEN DATEADD(MM, 1, StartDate) ELSE StartDate END AS StartDate, Type
	FROM	cf_SurveyMonthlyChargeback WITH (NOLOCK)
	WHERE	(@i_CommServUniqueID = 0 OR CommservUniqueId = @i_CommServUniqueID) AND StartDate IS NOT NULL
	ORDER BY Type ASC, StartDate DESC
	RETURN
END
IF OBJECT_ID('tempdb.dbo.#MonthlyResult') IS NOT NULL DROP TABLE #MonthlyResult
IF OBJECT_ID('tempdb.dbo.#FrontEndCopy') IS NOT NULL DROP TABLE #FrontEndCopy
IF OBJECT_ID('tempdb.dbo.#ClientGroup') IS NOT NULL DROP TABLE #ClientGroup
IF OBJECT_ID('tempdb.dbo.#ClientGroupsString') IS NOT NULL DROP TABLE #ClientGroupsString
IF OBJECT_ID('tempdb.dbo.#SubclientTags') IS NOT NULL DROP TABLE #SubclientTags
IF OBJECT_ID('tempdb.dbo.#SubclientCopyCost') IS NOT NULL DROP TABLE #SubclientCopyCost
IF OBJECT_ID('tempdb.dbo.#ClientCopyCost') IS NOT NULL DROP TABLE #ClientCopyCost
IF OBJECT_ID('tempdb.dbo.#ClientCost') IS NOT NULL DROP TABLE #ClientCost
IF OBJECT_ID('tempdb.dbo.#Cost') IS NOT NULL DROP TABLE #Cost
DECLARE @i_fromLogDate DATETIME = CASE @i_Type WHEN 1 THEN DATEADD(MONTH, 1, @i_StartDate) WHEN 2 THEN DATEADD(WEEK, 1, @i_StartDate) WHEN 4 THEN DATEADD(DAY, 1, @i_StartDate) END
CREATE	TABLE #MonthlyResult (
		CommServUniqueId INT,
		CommCellId INT,
		CustomerName NVARCHAR(256),
		CommServName NVARCHAR(256),
		CommServTZ VARCHAR(256),
		SurveyResultId BIGINT,
		StartDate DATETIME,
		CGABefore DATETIME,
		TagToCopy INT)
IF	@i_Type = 1 AND DAY(@i_StartDate) > 1 SET @i_StartDate = DATEADD(MM, -1, @i_StartDate) -- Monthly
ELSE
IF	@i_Type = 2 SET @i_QueryId = 162 -- Weekly
ELSE
IF	@i_Type = 4 SET @i_QueryId = 72  -- Daily
IF @i_CommServUniqueID > 0
	INSERT	INTO #MonthlyResult
	SELECT	R.CommServUniqueId, R.CommCellId, R.CustomerName, R.DisplayName, R.CommServTimeZone, R.SurverResultId, @i_StartDate, @i_fromLogDate, 0
	FROM	dbo.cf_SurveyResultFuncV1(@i_QueryId, @i_CommServUniqueID, 0, @i_StartDate, NULL, @i_UserId,@i_GroupId) R
			INNER JOIN (
				SELECT	MAX(SurveyResultId) AS SurveyResultId
				FROM	cf_SurveyMonthlyChargeback WITH (NOLOCK)
				WHERe	CommServUniqueId = @i_CommServUniqueID AND StartDate = @i_StartDate AND Type = @i_Type
				) S ON R.SurverResultId = S.SurveyResultId
ELSE
	INSERT	INTO #MonthlyResult
	SELECT	R.CommServUniqueId, R.CommCellId, R.CustomerName, R.DisplayName, R.CommServTimeZone, R.SurverResultId, @i_StartDate, @i_fromLogDate, 0
	FROM	dbo.cf_SurveyResultFuncV1(@i_QueryId, @i_CommServUniqueID, 0, @i_StartDate, NULL, @i_UserId,@i_GroupId) R
			INNER JOIN (
				SELECT	CommServUniqueId, MAX(SurveyResultId) AS SurveyResultId
				FROM	cf_SurveyMonthlyChargeback WITH (NOLOCK)
				WHERe	StartDate = @i_StartDate AND Type = @i_Type
				GROUP BY CommServUniqueId
				) S ON R.SurverResultId = S.SurveyResultId
IF @i_fromLogDate IS NOT NULL
	UPDATE	R SET CGABefore = NULL
	FROM	#MonthlyResult R
		INNER JOIN (
			SELECT	CommservUniqueId
			FROM	cf_SurveyClientGroupAssociation WITH (NOLOCK)
			WHERE	(@i_CommServUniqueID = 0 OR CommservUniqueId = @i_CommServUniqueID)
			GROUP BY CommservUniqueId
			HAVING MIN(ISNULL(AddedDate, '01/01/1970')) > @i_fromLogDate
			) T ON R.CommservUniqueId = T.CommservUniqueId
CREATE INDEX MonthlyResult_idx ON #MonthlyResult (SurveyResultId)
CREATE TABLE #ClientGroup (CommServUniqueId INT, ClientGroupId INT, ClientGroupName NVARCHAR(256), TenantGUID UNIQUEIDENTIFIER, TenantName NVARCHAR(256))
IF @i_AggregateLevel = 6 OR ISNULL(@i_TenantName, '') <> ''
BEGIN
	INSERT INTO #ClientGroup
	SELECT	DISTINCT CG.CommservUniqueId, CG.ClientGroupId, CG.ClientGroupName, 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 #MonthlyResult R ON CG.CommservUniqueId = R.CommservUniqueId
	WHERE	T.DeletedDate IS NULL AND (T.TenantName = @i_TenantName OR ISNULL(@i_TenantName, '') = '')
			AND (R.CGABefore IS NULL AND CG.DeletedDate IS NULL
			  OR R.CGABefore IS NOT NULL AND (CG.AddedDate IS NULL OR CG.AddedDate < R.CGABefore) AND (CG.DeletedDate IS NULL OR CG.DeletedDate >= R.CGABefore))
END
ELSE
IF @i_AggregateLevel = 3 OR ISNULL(@i_ClientGroupName, '') <> ''
BEGIN
	INSERT INTO #ClientGroup
	SELECT	CG.CommservUniqueId, CG.ClientGroupId, CG.ClientGroupName, NULL, ''
	FROM	cf_SurveyClientGroup CG WITH (NOLOCK)
			INNER JOIN #MonthlyResult R ON CG.CommservUniqueId = R.CommservUniqueId
	WHERE	(CG.ClientGroupName = @i_ClientGroupName OR ISNULL(@i_ClientGroupName, '') = '')
			AND (R.CGABefore IS NULL AND CG.DeletedDate IS NULL
			OR R.CGABefore IS NOT NULL AND (CG.AddedDate IS NULL OR CG.AddedDate < R.CGABefore) AND (CG.DeletedDate IS NULL OR CG.DeletedDate >= R.CGABefore))
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 #MonthlyResult R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate) T
ELSE
IF @i_Options&8 = 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, 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 #MonthlyResult R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate) T
ELSE
	INSERT INTO #FrontEndCopy
	SELECT	U.CommServUniqueId, U.SurveyResultId, U.ClientId, 106, U.AppId, U.CopyId,
			C.IsDefaultCopy, C.IsSnapCopy, 1,
			C.PolicyId, CASE WHEN C.IsDefaultCopy > 0 THEN U.CopyId ELSE 0 END, FrontEndSize
	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 #MonthlyResult R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate
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)
-- 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'
-- 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 #ClientGroupsString (CommservUniqueId INT, ClientId INT, ClientGroups NVARCHAR(MAX))
IF	ISNULL(@i_ClientGroupName, '')  <> '' OR ISNULL(@i_TenantName, '') <> ''
	INSERT INTO #ClientGroupsString
	SELECT	CGA.CommservUniqueId, CGA.ClientId, CG.ClientGroupName
	FROM	#ClientGroup CG
			INNER JOIN cf_SurveyClientGroupAssociation CGA
				ON CG.CommservUniqueId = CGA.CommservUniqueId AND CG.ClientGroupId = CGA.ClientGroupId
			INNER JOIN #MonthlyResult R ON CGA.CommservUniqueId = R.CommservUniqueId
	WHERE	R.CGABefore IS NULL AND CGA.DeletedDate IS NULL
		 OR R.CGABefore IS NOT NULL AND (CGA.AddedDate IS NULL OR CGA.AddedDate < R.CGABefore) AND (CGA.DeletedDate IS NULL OR CGA.DeletedDate >= R.CGABefore)
ELSE
	EXEC GetClientGroupsString @i_CommServUniqueID, 0, @i_fromLogDate
CREATE CLUSTERED INDEX #ClientGroupsString_idx ON #ClientGroupsString (CommservUniqueId, ClientId)
DECLARE	@i_FrontEndBackupTBCost		FLOAT = 0
DECLARE	@i_FrontEndArchiveTBCost	FLOAT = 0
DECLARE	@i_PrimaryAppTBCost			FLOAT = 0
DECLARE	@i_DataProtectedTBCost		FLOAT = 0
DECLARE	@i_DataWrittenTBCost		FLOAT = 0
DECLARE	@i_TotalProtectedAppTBCost	FLOAT = 0
DECLARE	@i_TotalDataOnMediaTBCost	FLOAT = 0
DECLARE	@showFrontEndBackupCost		INT = 0
DECLARE	@showFrontEndArchiveCost	INT = 0
DECLARE	@showPrimaryAppCost			INT = 0
DECLARE	@showDataProtectedCost		INT = 0
DECLARE	@showDataWrittenCost		INT = 0
DECLARE	@showTotalProtectedAppCost	INT = 0
DECLARE	@showTotalDataOnMediaCost	INT = 0
DECLARE	@i_BasePricePerClient		FLOAT = 0
DECLARE	@i_BasePricePerSubclient	FLOAT = 0
DECLARE	@i_DiscountLevel			INT   = -1 -- -1 for No Discount, 0 for Subclient, 1 for Client
DECLARE	@i_DiscountTB				FLOAT = 0
DECLARE	@i_DiscountPct				FLOAT = 0
DECLARE	@i_TagId					INT = 0
DECLARE	@NumOfCostColumns			INT = 0
DECLARE	@ShowBasePrice				INT = 0
DECLARE	@ShowDiscount				INT = 0
DECLARE	@ShowTotalCost				INT = 1
DECLARE	@UseTagToCopy				INT = 0
DECLARE	@UseTagToClient				INT = 0
DECLARE	@ClientBasePriceTag			INT = 0
DECLARE	@SubclientBasePriceTag		INT = 0
DECLARE	@ClientDiscountTag			INT = 0
DECLARE	@SubclientDiscountTag		INT = 0
SELECT	@i_FrontEndBackupTBCost = FrontEndTBCost, @i_FrontEndArchiveTBCost = FrontEndArchiveTBCost,
		@i_PrimaryAppTBCost = PrimaryAppTBCost, @i_DataProtectedTBCost = DataProtectedTBCost, @i_DataWrittenTBCost = DataWrittenCostPerTB,
		@i_TotalProtectedAppTBCost = TotalProtectedAppTBCost, @i_TotalDataOnMediaTBCost = TotalDataOnMediaTBCost,
		@i_BasePricePerClient = AdminCostPerClient, @i_BasePricePerSubclient = AdminCostPerSubclient,
		@i_DiscountLevel = DiscountLevel, @i_DiscountTB = DiscountSizeTB, @i_DiscountPct = DiscountPercentage
FROM	cf_GlobalCostParamView
IF	@i_BasePricePerClient > 0 OR @i_BasePricePerSubclient > 0
	SET	@ShowBasePrice = 1
IF @i_DiscountLevel IN (0, 1) AND @i_DiscountTB > 0 AND @i_DiscountPct > 0
	SET @ShowDiscount = 1
IF @i_FrontEndBackupTBCost > 0    SET @showFrontEndBackupCost = 1
IF @i_FrontEndArchiveTBCost > 0   SET @showFrontEndArchiveCost = 1
IF @i_PrimaryAppTBCost > 0	      SET @showPrimaryAppCost = 1
IF @i_DataProtectedTBCost > 0     SET @showDataProtectedCost = 1
IF @i_DataWrittenTBCost > 0       SET @showDataWrittenCost = 1
IF @i_TotalProtectedAppTBCost > 0 SET @showTotalProtectedAppCost = 1
IF @i_TotalDataOnMediaTBCost > 0  SET @showTotalDataOnMediaCost = 1
SET @NumOfCostColumns += (@showFrontEndBackupCost + @showFrontEndArchiveCost
	+ @showPrimaryAppCost + @showDataProtectedCost + @showDataWrittenCost
	+ @showTotalProtectedAppCost + @showTotalDataOnMediaCost)
UPDATE	#MonthlyResult SET TagToCopy = 1
WHERE	CommservUniqueId IN (SELECT DISTINCT CommservUniqueId FROM cf_SurveyBillingEntityToCopy WHERE CommservUniqueId > 0 AND CopyId > 0)
IF	EXISTS (SELECT * FROM #MonthlyResult WHERE TagToCopy = 1)
	SET @UseTagToCopy = 1
IF	@i_TagName <> ''
	SELECT	@i_TagId = ID FROM cf_SurveyBillingEntity WHERE Name = @i_TagName
CREATE TABLE #SubclientTags (CommservUniqueId INT, CopyId INT,
			 ClientGroupId INT, ClientId INT, AppTypeId INT, InstanceId INT, BackupsetId INT, AppId INT,
			 TagId INT, TagLevel INT, Price FLOAT NULL, BasePriceLevel INT NULL, BasePrice FLOAT NULL,
			 DiscountLevel INT NULL, DiscountTB FLOAT NULL, DiscountPct FLOAT NULL)
IF	EXISTS (SELECT * FROM cf_SurveyBillingEntityMember WHERE CommservUniqueId > 0 OR ClientGroupId > 0 OR ClientId > 0 OR AppTypeId > 0)
BEGIN
	CREATE TABLE #ClientTags (CommservUniqueId INT, ClientGroupId INT, ClientId INT, TagId INT, TagLevel INT)
	INSERT	INTO #ClientTags
	SELECT	M.CommservUniqueId, 0, M.ClientId, M.EntityId, 5
	FROM	cf_SurveyBillingEntityMember M
	WHERE	M.ClientId > 0 AND M.AppTypeId = 0 AND @i_CommServUniqueID IN (0, CommservUniqueId) AND @i_TagId IN (0, M.EntityId)
	INSERT	INTO #ClientTags
	SELECT	M.CommservUniqueId, M.ClientGroupId, C.ClientId, M.EntityId, 6
	FROM	cf_SurveyBillingEntityMember M
			INNER JOIN cf_SurveyClientGroupAssociation C ON M.CommservUniqueId = C.CommservUniqueId
				AND M.ClientGroupId = C.ClientGroupId AND M.ClientId = 0 AND M.AppTypeId = 0
				AND @i_CommServUniqueID IN (0, M.CommServUniqueId)
			INNER JOIN #MonthlyResult R ON C.CommservUniqueId = R.CommservUniqueId
			LEFT OUTER JOIN #ClientTags T ON C.CommservUniqueId = T.CommservUniqueId AND C.ClientId = T.ClientId
	WHERE	T.ClientId IS NULL AND @i_TagId IN (0, M.EntityId)
		AND (R.CGABefore IS NULL AND C.DeletedDate IS NULL
		  OR R.CGABefore IS NOT NULL AND (C.AddedDate IS NULL OR C.AddedDate < R.CGABefore) AND (C.DeletedDate IS NULL OR C.DeletedDate >= R.CGABefore))
	CREATE INDEX #ClientTags_CommservUniqueId_ClientId_idx ON #ClientTags (CommservUniqueId, ClientId)
	INSERT	INTO #ClientTags
	SELECT	M.CommservUniqueId, 0, C.ClientId, M.EntityId, 7
	FROM	(SELECT	CommservUniqueId, EntityId
			FROM	cf_SurveyBillingEntityMember
			WHERE	CommservUniqueId > 0 AND ClientGroupId <= 0 AND ClientId = 0 AND AppTypeId = 0 AND @i_CommServUniqueID IN (0, CommservUniqueId) AND @i_TagId IN (0, EntityId)
			) M
			INNER JOIN
			(SELECT	DISTINCT U.CommservUniqueId, U.ClientId
			FROM	cf_SurveyMonthlyChargeback AS U INNER JOIN #MonthlyResult R
					ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate AND U.Type = @i_Type
			) C	ON M.CommservUniqueId = C.CommservUniqueId
			LEFT OUTER JOIN #ClientTags T ON M.CommservUniqueId = T.CommservUniqueId AND C.ClientId = T.ClientId
	WHERE	T.ClientId IS NULL
	INSERT	INTO #ClientTags
	SELECT	M.CommservUniqueId, 0, M.ClientId, NULL, NULL
	FROM	(SELECT	DISTINCT CommservUniqueId, ClientId
			FROM	cf_SurveyBillingEntityMember
			WHERE	ClientId > 0 AND AppTypeId > 0 AND @i_CommServUniqueID IN (0, CommservUniqueId) AND @i_TagId IN (0, EntityId)
			) M
			LEFT OUTER JOIN #ClientTags C ON M.CommServUniqueId = C.CommservUniqueId AND M.ClientId = C.ClientId
	WHERE	C.ClientId IS NULL
	INSERT	INTO #SubclientTags
	SELECT	U.CommservUniqueId, 0, C.ClientGroupId, U.ClientId, A.AppTypeId, A.InstanceId, A.BackupsetId, U.AppId, C.TagId, C.TagLevel, NULL, NULL, NULL, NULL, NULL, NULL
	FROM	#ClientTags C
			INNER JOIN #MonthlyResult R ON C.CommServUniqueId = R.CommServUniqueId
			INNER JOIN cf_SurveyMonthlyChargeback AS U ON R.SurveyResultId = U.SurveyResultId AND C.ClientId = U.ClientId AND U.StartDate = R.StartDate AND U.Type = @i_Type
			INNER JOIN cf_SurveySubclientAssociation A ON U.CommServUniqueId = A.CommServUniqueId AND U.AppId = A.AppId AND A.AppId > 0
	WHERE	((@i_Options & 1)  = 0 OR (A.SubclientStatus & (2|4)) = 0)
		AND ((@i_Options & 16) > 0 OR (A.SpecialTypeFlags & (2|4|8)) = 0 AND A.AppTypeId <> 1000)
		AND ((@i_Options & 32) = 0 OR A.BackupDisabled = 0)
	GROUP BY U.CommservUniqueId, C.ClientGroupId, U.ClientId, A.AppTypeId, A.InstanceId, A.BackupsetId, U.AppId, C.TagId, C.TagLevel
	-- Tags directly associated to Agent Type
	CREATE TABLE #AppTypeTags (CommservUniqueId INT, ClientId INT, AppTypeId INT, TagId INT)
	INSERT	INTO #AppTypeTags
	SELECT	R.CommservUniqueId, M.ClientId, M.AppTypeId, M.EntityId
	FROM	#MonthlyResult R
			INNER JOIN cf_SurveyBillingEntityMember M ON R.CommServUniqueId = M.CommservUniqueId OR M.CommservUniqueId = 0
			LEFT OUTER JOIN #ClientTags C ON R.CommServUniqueId = C.CommservUniqueId AND M.ClientId = C.ClientId
	WHERE	M.ClientId = 0 AND M.AppTypeId > 0 AND @i_TagId IN (0, M.EntityId) AND C.ClientId IS NULL
	GROUP BY R.CommservUniqueId, M.ClientId, M.AppTypeId, M.EntityId
	INSERT	INTO #SubclientTags
	SELECT	U.CommservUniqueId, 0, 0, U.ClientId, A.AppTypeId, A.InstanceId, A.BackupsetId, U.AppId, T.TagId, 4, NULL, NULL, NULL, NULL, NULL, NULL
	FROM	#MonthlyResult R
			INNER JOIN cf_SurveyMonthlyChargeback AS U ON R.SurveyResultId = U.SurveyResultId AND U.StartDate = R.StartDate AND U.Type = @i_Type
			INNER JOIN cf_SurveySubclientAssociation A ON U.CommServUniqueId = A.CommServUniqueId AND U.AppId = A.AppId AND A.AppId > 0
			INNER JOIN #AppTypeTags T ON A.CommservUniqueId = T.CommservUniqueId AND A.AppTypeId = T.AppTypeId
	WHERE	((@i_Options & 1) = 0 OR (A.SubclientStatus & (2|4)) = 0)
		AND ((@i_Options & 16) > 0 OR (A.SpecialTypeFlags & (2|4|8)) = 0 AND A.AppTypeId <> 1000)
		AND ((@i_Options & 32) = 0 OR A.BackupDisabled = 0)
	GROUP BY U.CommservUniqueId, U.ClientId, A.AppTypeId, A.InstanceId, A.BackupsetId, U.AppId, T.TagId
	DROP TABLE #AppTypeTags
	UPDATE	#SubclientTags SET TagId = M.EntityId, TagLevel = 4
	FROM	#SubclientTags T INNER JOIN cf_SurveyBillingEntityMember M
			 ON T.AppTypeId = M.AppTypeId AND M.ClientId = 0 AND (M.CommservUniqueId = 0 OR T.CommservUniqueId = M.CommservUniqueId)
	WHERE	T.TagLevel IN (5,6,7) AND @i_TagId IN (0, M.EntityId)
	DROP TABLE #ClientTags
	CREATE INDEX #SubclientTags_CommservUniqueId_Client_AppId_idx ON #SubclientTags (CommservUniqueId, ClientId, AppId)
	UPDATE	#SubclientTags SET TagId = M.EntityId, TagLevel = 4
	FROM	#SubclientTags T INNER JOIN cf_SurveyBillingEntityMember M
			 ON T.CommservUniqueId = M.CommservUniqueId AND T.ClientId = M.ClientId AND T.AppTypeId = M.AppTypeId AND M.InstanceId = 0
			 AND @i_TagId IN (0, M.EntityId)
	UPDATE	#SubclientTags SET TagId = M.EntityId, TagLevel = 3
	FROM	#SubclientTags T INNER JOIN cf_SurveyBillingEntityMember M
			 ON T.CommservUniqueId = M.CommservUniqueId AND T.ClientId = M.ClientId AND T.AppTypeId = M.AppTypeId AND T.InstanceId = M.InstanceId
			 AND M.BackupsetId = 0
			 AND @i_TagId IN (0, M.EntityId)
	UPDATE	#SubclientTags SET TagId = M.EntityId, TagLevel = 2
	FROM	#SubclientTags T INNER JOIN cf_SurveyBillingEntityMember M
			 ON T.CommservUniqueId = M.CommservUniqueId AND T.ClientId = M.ClientId AND T.AppTypeId = M.AppTypeId AND T.InstanceId = M.InstanceId
			 AND T.BackupsetId = M.BackupsetId AND M.AppId = 0
			 AND @i_TagId IN (0, M.EntityId)
	UPDATE	#SubclientTags SET TagId = M.EntityId, TagLevel = 1
	FROM	#SubclientTags T INNER JOIN cf_SurveyBillingEntityMember M
			 ON T.CommservUniqueId = M.CommservUniqueId AND T.ClientId = M.ClientId AND T.AppTypeId = M.AppTypeId AND T.InstanceId = M.InstanceId
			 AND T.BackupsetId = M.BackupsetId AND T.AppId = M.AppId
			 AND @i_TagId IN (0, M.EntityId)
	-- Apply tag associated by the VSA subclient to VM which is not associated to a tag at client level
	UPDATE	#SubclientTags SET TagId = M.EntityId, TagLevel = 5
	FROM	#SubclientTags T INNER JOIN cf_SurveyBillingEntityMember M
			 ON T.CommservUniqueId = M.CommservUniqueId AND T.ClientId <> M.ClientId AND T.AppTypeId = M.AppTypeId AND T.AppId = M.AppId
			 AND T.AppTypeId = 106 AND (T.TagId = 0 OR T.TagLevel <> 5)
		 	 AND @i_TagId IN (0, M.EntityId)
END
IF	EXISTS (SELECT * FROM #SubclientTags)
	SET @UseTagToClient = 1
IF @UseTagToCopy = 1
BEGIN
	INSERT	INTO #SubclientTags
	SELECT	E.CommservUniqueId, E.CopyId, 0, 0, 0, 0, 0, 0,
			E.EntityId, 8, NULL, NULL, NULL, NULL, NULL, NULL
	FROM	cf_SurveyBillingEntityToCopy E
			INNER JOIN
			(SELECT	DISTINCT U.CommservUniqueId, U.CopyId
			FROM	cf_SurveyMonthlyChargeback AS U INNER JOIN #MonthlyResult R
					ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate AND U.Type = @i_Type
			) C	ON E.CommservUniqueId = C.CommservUniqueId AND E.CopyId = C.CopyId
END
IF @UseTagToCopy = 1 OR @UseTagToClient = 1
	UPDATE	#SubclientTags SET Price = P.PricePerTB, BasePriceLevel = P.BasePriceLevel, BasePrice = P.BasePrice,
			DiscountLevel = P.DiscountLevel, DiscountTB = P.DiscountTB, DiscountPct = P.DiscountPct
	FROM	#SubclientTags T INNER JOIN cf_SurveyPriceTag P ON T.TagId = P.TagNameId AND P.TagType = 0 AND P.UsageType = 0
IF	EXISTS (SELECT * FROM #SubclientTags WHERE BasePriceLevel = 0)
	SET @SubclientBasePriceTag = 1
IF	EXISTS (SELECT * FROM #SubclientTags WHERE BasePriceLevel = 1)
	SET @ClientBasePriceTag = 1
IF	@SubclientBasePriceTag = 1 OR @ClientBasePriceTag = 1
	SET @ShowBasePrice = 1
IF	EXISTS (SELECT * FROM #SubclientTags WHERE DiscountLevel = 0)
	SET @SubclientDiscountTag = 1
IF	EXISTS (SELECT * FROM #SubclientTags WHERE DiscountLevel = 1)
	SET @ClientDiscountTag = 1
IF	@SubclientDiscountTag = 1 OR @ClientDiscountTag = 1
	SET @ShowDiscount = 1
IF	@ShowBasePrice = 1 SET @NumOfCostColumns += 1
IF	@ShowDiscount  = 1 SET @NumOfCostColumns += 1
IF	@NumOfCostColumns < 2
	SET	@ShowTotalCost = 0
CREATE TABLE #SubclientCopyCost
(
	CommServUniqueId INT, ClientId INT, AppId INT, CopyId INT, IsFrontEndCopy INT, TagId INT, TagLevel INT,
	FrontEndBackupSize BIGINT, FrontEndBackupCost FLOAT, FrontEndArchiveSize BIGINT, FrontEndArchiveCost FLOAT,
	PrimaryAppSize BIGINT, PrimaryAppCost FLOAT, DataProtected BIGINT, DataProtectedCost FLOAT, DataWritten BIGINT, DataWrittenCost FLOAT,
	TotalProtectedApp BIGINT, TotalProtectedAppCost FLOAT, TotalDataOnMedia BIGINT, TotalDataOnMediaCost FLOAT,
	BasePrice FLOAT, Discount FLOAT
)
CREATE TABLE #ClientCopyCost
(
	CommServUniqueId INT, ClientId INT, CopyId INT, IsFrontEndCopy INT, TagId INT, TagLevel INT,
	FrontEndBackupSize BIGINT, FrontEndBackupCost FLOAT, FrontEndArchiveSize BIGINT, FrontEndArchiveCost FLOAT,
	PrimaryAppSize BIGINT, PrimaryAppCost FLOAT, DataProtected BIGINT, DataProtectedCost FLOAT, DataWritten BIGINT, DataWrittenCost FLOAT,
	TotalProtectedApp BIGINT, TotalProtectedAppCost FLOAT, TotalDataOnMedia BIGINT, TotalDataOnMediaCost FLOAT,
	nSubclients INT, BasePrice FLOAT, TagBasePriceLevel INT, Discount FLOAT, TagDiscountLevel INT
)
CREATE TABLE #ClientCost
(
	CommServUniqueId INT, ClientId INT,
	FrontEndBackupSize BIGINT, FrontEndBackupCost FLOAT, FrontEndArchiveSize BIGINT, FrontEndArchiveCost FLOAT,
	PrimaryAppSize BIGINT, PrimaryAppCost FLOAT, DataProtected BIGINT, DataProtectedCost FLOAT, DataWritten BIGINT, DataWrittenCost FLOAT,
	TotalProtectedApp BIGINT, TotalProtectedAppCost FLOAT, TotalDataOnMedia BIGINT, TotalDataOnMediaCost FLOAT,
	nSubclients INT, BasePrice FLOAT, Discount FLOAT
)
CREATE TABLE #TagPrice
(
	TagId INT, SizeType INT, Price FLOAT, BasePriceLevel INT, BasePrice FLOAT, DiscountLevel INT, DiscountTB FLOAT, DiscountPct FLOAT
)
INSERT	INTO #TagPrice
SELECT	P.TagNameId, P.UsageType, PricePerTB, BasePriceLevel, BasePrice, DiscountLevel, DiscountTB, DiscountPct
FROM	(SELECT DISTINCT TagId FROM #SubclientTags) T
		INNER JOIN cf_SurveyPriceTag P ON T.TagId = P.TagNameId AND P.TagType = 0 AND P.UsageType IN (1, 2, 3, 4)
UPDATE	#TagPrice
SET Price = CASE SizeType
			WHEN 1 THEN @i_FrontEndBackupTBCost
			WHEN 2 THEN @i_FrontEndArchiveTBCost
			WHEN 3 THEN @i_DataProtectedTBCost
			WHEN 4 THEN @i_DataWrittenTBCost
		END
WHERE	Price < 0
IF @i_AggregateLevel IN (0, 1, 2, 3, 4, 5, 6)
BEGIN
  IF EXISTS (SELECT * FROM #MonthlyResult WHERE TagToCopy = 0)
	INSERT	INTO #SubclientCopyCost
	SELECT 	U.CommServUniqueId, U.ClientId, U.AppId, U.CopyId, FEC.IsFrontEndCopy, ISNULL(T.TagId, 0), ISNULL(T.TagLevel, 0),
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0) <> 1 THEN U.FrontEndSize ELSE 0 END,
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0) <> 1 THEN @BytesToTB*U.FrontEndSize*ISNULL(T.Price, @i_FrontEndBackupTBCost) ELSE 0 END,
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0)  = 1 THEN U.FrontEndSize ELSE 0 END,
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0)  = 1 THEN @BytesToTB*U.FrontEndSize*ISNULL(T.Price, @i_FrontEndArchiveTBCost) ELSE 0 END,
			CASE WHEN U.CopyId = FEC.PrimaryCopyId THEN U.DataProtected ELSE 0 END,
			CASE WHEN U.CopyId = FEC.PrimaryCopyId THEN @BytesToTB*U.DataProtected*ISNULL(T.Price, @i_PrimaryAppTBCost) ELSE 0 END,
			U.DataProtected,
			@BytesToTB*U.DataProtected*ISNULL(T.Price, @i_DataProtectedTBCost),
			U.DataWritten,
			@BytesToTB*U.DataWritten*ISNULL(T.Price, @i_DataWrittenTBCost),
			U.TotalProtectedApp,
			@BytesToTB*U.TotalProtectedApp*ISNULL(T.Price, @i_TotalProtectedAppTBCost),
			U.TotalDataOnMedia,
			@BytesToTB*U.TotalDataOnMedia*ISNULL(T.Price, @i_TotalDataOnMediaTBCost),
			CASE WHEN FEC.IsFrontEndCopy = 0 THEN 0 WHEN ISNULL(T.BasePriceLevel, -1) = 0 THEN T.BasePrice ELSE @i_BasePricePerSubclient END,
			0
	FROM	cf_SurveyMonthlyChargeback AS U WITH (NOLOCK)
			INNER JOIN #MonthlyResult R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate AND U.Type = @i_Type AND TagToCopy = 0
			INNER JOIN #FrontEndCopy FEC ON U.SurveyResultId = FEC.SurveyResultId AND U.ClientId = FEC.ClientId AND U.AppId = FEC.AppId AND U.CopyId = FEC.CopyId
			LEFT OUTER JOIN #SubclientTags T ON U.CommServUniqueId = T.CommServUniqueId AND U.ClientId = T.ClientId AND U.AppId = T.AppId
	WHERE	@i_TagId = 0 OR ISNULL(T.TagId, 0) = @i_TagId
  IF EXISTS (SELECT * FROM #MonthlyResult WHERE TagToCopy = 1)
	INSERT	INTO #SubclientCopyCost
	SELECT 	U.CommServUniqueId, U.ClientId, U.AppId, U.CopyId, FEC.IsFrontEndCopy, ISNULL(T.TagId, 0), ISNULL(T.TagLevel, 0),
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0) <> 1 THEN U.FrontEndSize ELSE 0 END,
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0) <> 1 THEN @BytesToTB*U.FrontEndSize*ISNULL(T.Price, @i_FrontEndBackupTBCost) ELSE 0 END,
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0)  = 1 THEN U.FrontEndSize ELSE 0 END,
			CASE WHEN FEC.IsFrontEndCopy = 1 AND ISNULL(U.FrontEndSizeType, 0)  = 1 THEN @BytesToTB*U.FrontEndSize*ISNULL(T.Price, @i_FrontEndArchiveTBCost) ELSE 0 END,
			CASE WHEN U.CopyId = FEC.PrimaryCopyId THEN U.DataProtected ELSE 0 END,
			CASE WHEN U.CopyId = FEC.PrimaryCopyId THEN @BytesToTB*U.DataProtected*ISNULL(T.Price, @i_PrimaryAppTBCost) ELSE 0 END,
			U.DataProtected,
			@BytesToTB*U.DataProtected*ISNULL(T.Price, @i_DataProtectedTBCost),
			U.DataWritten,
			@BytesToTB*U.DataWritten*ISNULL(T.Price, @i_DataWrittenTBCost),
			U.TotalProtectedApp,
			@BytesToTB*U.TotalProtectedApp*ISNULL(T.Price, @i_TotalProtectedAppTBCost),
			U.TotalDataOnMedia,
			@BytesToTB*U.TotalDataOnMedia*ISNULL(T.Price, @i_TotalDataOnMediaTBCost),
			CASE WHEN FEC.IsFrontEndCopy = 0 THEN 0 WHEN ISNULL(T.BasePriceLevel, -1) = 0 THEN T.BasePrice ELSE @i_BasePricePerSubclient END,
			0
	FROM	cf_SurveyMonthlyChargeback AS U WITH (NOLOCK)
			INNER JOIN #MonthlyResult R ON U.SurveyResultId = R.SurveyResultId AND U.StartDate = R.StartDate AND U.Type = @i_Type AND TagToCopy = 1
			INNER JOIN #FrontEndCopy FEC ON U.SurveyResultId = FEC.SurveyResultId AND U.ClientId = FEC.ClientId AND U.AppId = FEC.AppId AND U.CopyId = FEC.CopyId
			LEFT OUTER JOIN #SubclientTags T ON U.CommServUniqueId = T.CommServUniqueId AND U.CopyId = T.CopyId
	WHERE	@i_TagId = 0 OR ISNULL(T.TagId, 0) = @i_TagId
	-- Exclude Deconfigured Clients/Subclients
	IF	(@i_Options & 1) = 1
	BEGIN
		DELETE	S
		FROM	#SubclientCopyCost 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	#SubclientCopyCost S INNER JOIN cf_SurveyClient CL
				ON S.CommServUniqueId = CL.CommServUniqueId AND S.ClientId = CL.ClientId
		WHERE	CL.InstallStatus > 0 OR CL.Deleted = 1
	END
	-- Exclude DR/DDB/Index subclients
	IF	(@i_Options & 16) = 0
	BEGIN
		DELETE	S
		FROM	#SubclientCopyCost S INNER JOIN cf_SurveySubclientAssociation A
				ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId AND A.AppId > 0
		WHERE	A.AppTypeId = 1000 OR (A.SpecialTypeFlags & (2|4|8)) > 0
	END
	-- Exclude Backup Activity Disabled Subclients
	IF	(@i_Options & 32) = 32
	BEGIN
		DELETE	S
		FROM	#SubclientCopyCost S INNER JOIN cf_SurveySubclientAssociation A
				ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId AND A.AppId > 0
		WHERE	A.BackupDisabled = 1
	END
	IF	@SubclientDiscountTag = 1
	UPDATE	#SubclientCopyCost
	SET		Discount = -0.01*DiscountPct*Price*(
				(CASE WHEN @BytesToTB*FrontEndBackupSize < DiscountTB THEN @BytesToTB*FrontEndBackupSize  ELSE DiscountTB END)*@showFrontEndBackupCost
			  + (CASE WHEN @BytesToTB*FrontEndArchiveSize< DiscountTB THEN @BytesToTB*FrontEndArchiveSize ELSE DiscountTB END)*@showFrontEndArchiveCost
			  +	(CASE WHEN @BytesToTB*PrimaryAppSize     < DiscountTB THEN @BytesToTB*PrimaryAppSize      ELSE DiscountTB END)*@showPrimaryAppCost
			  +	(CASE WHEN @BytesToTB*DataProtected      < DiscountTB THEN @BytesToTB*DataProtected       ELSE DiscountTB END)*@showDataProtectedCost
			  +	(CASE WHEN @BytesToTB*DataWritten        < DiscountTB THEN @BytesToTB*DataWritten         ELSE DiscountTB END)*@showDataWrittenCost
			  +	(CASE WHEN @BytesToTB*TotalProtectedApp  < DiscountTB THEN @BytesToTB*TotalProtectedApp   ELSE DiscountTB END)*@showTotalProtectedAppCost
			  +	(CASE WHEN @BytesToTB*TotalDataOnMedia   < DiscountTB THEN @BytesToTB*TotalDataOnMedia    ELSE DiscountTB END)*@showTotalDataOnMediaCost
			  )
	FROM	#SubclientCopyCost S
			INNER JOIN #SubclientTags T
			ON S.CommServUniqueId = T.CommServUniqueId AND S.CopyId = T.CopyId AND T.DiscountLevel = 0
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 1)
		UPDATE	T SET FrontEndBackupCost = @BytesToTB*T.FrontEndBackupSize*P.Price,
				Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN P.DiscountLevel <> 0 THEN 0 WHEN @BytesToTB*T.FrontEndBackupSize < P.DiscountTB THEN @BytesToTB*T.FrontEndBackupSize ELSE P.DiscountTB END)*@showFrontEndBackupCost
		FROM	#SubclientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND P.SizeType = 1
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 2)
		UPDATE	T SET FrontEndArchiveCost = @BytesToTB*T.FrontEndArchiveSize*P.Price,
				Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN P.DiscountLevel <> 0 THEN 0 WHEN @BytesToTB*T.FrontEndArchiveSize < P.DiscountTB THEN @BytesToTB*T.FrontEndArchiveSize ELSE P.DiscountTB END)*@showFrontEndArchiveCost
		FROM	#SubclientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND P.SizeType = 2
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 3)
		UPDATE	T
		SET		PrimaryAppCost = @BytesToTB*T.PrimaryAppSize*P.Price,
				DataProtectedCost = @BytesToTB*T.DataProtected*P.Price,
				TotalProtectedAppCost = @BytesToTB*T.TotalProtectedApp*P.Price,
				Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN P.DiscountLevel = 0 THEN 1 ELSE 0 END)*(
						  (CASE WHEN @BytesToTB*T.PrimaryAppSize < P.DiscountTB THEN @BytesToTB*T.PrimaryAppSize ELSE P.DiscountTB END)*@showPrimaryAppCost
						+ (CASE WHEN @BytesToTB*T.DataProtected  < P.DiscountTB THEN @BytesToTB*T.DataProtected  ELSE P.DiscountTB END)*@showDataProtectedCost
						+ (CASE WHEN @BytesToTB*T.TotalProtectedApp < P.DiscountTB THEN @BytesToTB*T.TotalProtectedApp ELSE P.DiscountTB END)*@showTotalProtectedAppCost
						)
		FROM	#SubclientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND P.SizeType = 3
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 4)
		UPDATE	T
		SET		DataWrittenCost = @BytesToTB*T.DataWritten*P.Price,
				TotalDataOnMediaCost = @BytesToTB*T.TotalDataOnMedia*P.Price,
				Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN P.DiscountLevel = 0 THEN 1 ELSE 0 END)*(
						  (CASE WHEN @BytesToTB*T.DataWritten < P.DiscountTB THEN @BytesToTB*T.DataWritten ELSE P.DiscountTB END)*@showDataWrittenCost
						+ (CASE WHEN @BytesToTB*T.TotalDataOnMedia < P.DiscountTB THEN @BytesToTB*T.TotalDataOnMedia ELSE P.DiscountTB END)*@showTotalDataOnMediaCost
						)
		FROM	#SubclientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND P.SizeType = 4
	IF	@i_DiscountLevel = 0
	UPDATE	#SubclientCopyCost
	SET		Discount = -0.01*@i_DiscountPct*(
				(CASE WHEN @BytesToTB*FrontEndBackupSize < @i_DiscountTB THEN @BytesToTB*FrontEndBackupSize  ELSE @i_DiscountTB END)*@i_FrontEndBackupTBCost
			  + (CASE WHEN @BytesToTB*FrontEndArchiveSize< @i_DiscountTB THEN @BytesToTB*FrontEndArchiveSize ELSE @i_DiscountTB END)*@i_FrontEndArchiveTBCost
			  +	(CASE WHEN @BytesToTB*PrimaryAppSize     < @i_DiscountTB THEN @BytesToTB*PrimaryAppSize      ELSE @i_DiscountTB END)*@i_PrimaryAppTBCost
			  +	(CASE WHEN @BytesToTB*DataProtected      < @i_DiscountTB THEN @BytesToTB*DataProtected       ELSE @i_DiscountTB END)*@i_DataProtectedTBCost
			  +	(CASE WHEN @BytesToTB*DataWritten        < @i_DiscountTB THEN @BytesToTB*DataWritten         ELSE @i_DiscountTB END)*@i_DataWrittenTBCost
			  +	(CASE WHEN @BytesToTB*TotalProtectedApp  < @i_DiscountTB THEN @BytesToTB*TotalProtectedApp   ELSE @i_DiscountTB END)*@i_TotalProtectedAppTBCost
			  +	(CASE WHEN @BytesToTB*TotalDataOnMedia   < @i_DiscountTB THEN @BytesToTB*TotalDataOnMedia    ELSE @i_DiscountTB END)*@i_TotalDataOnMediaTBCost
			  )
	FROM	#SubclientCopyCost
	WHERE	Discount = 0
END
IF @i_AggregateLevel IN (1, 2, 3, 4, 6)
BEGIN
	INSERT	INTO #ClientCopyCost
	SELECT	CommServUniqueId, ClientId, CopyId, IsFrontEndCopy,
			CASE WHEN TagLevel IN (5,6,7,8) THEN TagId ELSE 0 END,
			CASE WHEN TagLevel IN (5,6,7,8) THEN TagLevel ELSE 0 END,
			SUM(FrontEndBackupSize), SUM(FrontEndBackupCost),
			SUM(FrontEndArchiveSize),SUM(FrontEndArchiveCost),
			SUM(PrimaryAppSize),     SUM(PrimaryAppCost),
			SUM(DataProtected),      SUM(DataProtectedCost),
			SUM(DataWritten),        SUM(DataWrittenCost),
			SUM(TotalProtectedApp),  SUM(TotalProtectedAppCost),
			SUM(TotalDataOnMedia),   SUM(TotalDataOnMediaCost),
			SUM(IsFrontEndCopy),
			SUM(BasePrice),          0,
			SUM(Discount),           0
	FROM	#SubclientCopyCost
	WHERE	@i_TagId = 0 OR ISNULL(TagId, 0) = @i_TagId
	GROUP BY CommServUniqueId, ClientId, CopyId, IsFrontEndCopy,
			CASE WHEN TagLevel IN (5,6,7,8) THEN TagId ELSE 0 END,
			CASE WHEN TagLevel IN (5,6,7,8) THEN TagLevel ELSE 0 END
	UPDATE	#ClientCopyCost
	SET		BasePrice += T.BasePrice, TagBasePriceLevel = 1
	FROM	#ClientCopyCost C INNER JOIN #SubclientTags T
		 ON C.CommServUniqueId = T.CommServUniqueId AND C.ClientId = T.ClientId AND C.TagId = T.TagId AND T.BasePriceLevel = 1 AND IsFrontEndCopy = 1
	UPDATE	#ClientCopyCost
	SET		Discount += -0.01*DiscountPct*Price*(
				(CASE WHEN @BytesToTB*FrontEndBackupSize < DiscountTB THEN @BytesToTB*FrontEndBackupSize  ELSE DiscountTB END)*@showFrontEndBackupCost
			  + (CASE WHEN @BytesToTB*FrontEndArchiveSize< DiscountTB THEN @BytesToTB*FrontEndArchiveSize ELSE DiscountTB END)*@showFrontEndArchiveCost
			  +	(CASE WHEN @BytesToTB*PrimaryAppSize     < DiscountTB THEN @BytesToTB*PrimaryAppSize      ELSE DiscountTB END)*@showPrimaryAppCost
			  +	(CASE WHEN @BytesToTB*DataProtected      < DiscountTB THEN @BytesToTB*DataProtected       ELSE DiscountTB END)*@showDataProtectedCost
			  +	(CASE WHEN @BytesToTB*DataWritten        < DiscountTB THEN @BytesToTB*DataWritten         ELSE DiscountTB END)*@showDataWrittenCost
			  +	(CASE WHEN @BytesToTB*TotalProtectedApp  < DiscountTB THEN @BytesToTB*TotalProtectedApp   ELSE DiscountTB END)*@showTotalProtectedAppCost
			  +	(CASE WHEN @BytesToTB*TotalDataOnMedia   < DiscountTB THEN @BytesToTB*TotalDataOnMedia    ELSE DiscountTB END)*@showTotalDataOnMediaCost
			  ),
			  TagDiscountLevel = 1
	FROM	#ClientCopyCost C INNER JOIN #SubclientTags T
		 ON C.CommServUniqueId = T.CommServUniqueId AND C.ClientId = T.ClientId AND C.TagId = T.TagId AND T.DiscountLevel = 1
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 1) AND @showFrontEndBackupCost > 0
		UPDATE	T SET Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN @BytesToTB*T.FrontEndBackupSize < P.DiscountTB THEN @BytesToTB*T.FrontEndBackupSize ELSE P.DiscountTB END),
				TagDiscountLevel = 1
		FROM	#ClientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND T.TagDiscountLevel = 1 AND P.SizeType = 1
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 2) AND @showFrontEndArchiveCost > 0
		UPDATE	T SET Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN @BytesToTB*T.FrontEndArchiveSize < P.DiscountTB THEN @BytesToTB*T.FrontEndArchiveSize ELSE P.DiscountTB END),
				TagDiscountLevel = 1
		FROM	#ClientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND T.TagDiscountLevel = 1 AND P.SizeType = 2
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 3)
		UPDATE	T
		SET		Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN P.DiscountLevel = 0 THEN 1 ELSE 0 END)*(
						  (CASE WHEN @BytesToTB*T.PrimaryAppSize < P.DiscountTB THEN @BytesToTB*T.PrimaryAppSize ELSE P.DiscountTB END)*@showPrimaryAppCost
						+ (CASE WHEN @BytesToTB*T.DataProtected  < P.DiscountTB THEN @BytesToTB*T.DataProtected  ELSE P.DiscountTB END)*@showDataProtectedCost
						+ (CASE WHEN @BytesToTB*T.TotalProtectedApp < P.DiscountTB THEN @BytesToTB*T.TotalProtectedApp ELSE P.DiscountTB END)*@showTotalProtectedAppCost
						),
				TagDiscountLevel = 1
		FROM	#ClientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND T.TagDiscountLevel = 1 AND P.SizeType = 3
	IF	EXISTS (SELECT * FROM #TagPrice WHERE SizeType = 4)
		UPDATE	T
		SET		Discount += -0.01*P.DiscountPct*P.Price*(CASE WHEN P.DiscountLevel = 0 THEN 1 ELSE 0 END)*(
						  (CASE WHEN @BytesToTB*T.DataWritten < P.DiscountTB THEN @BytesToTB*T.DataWritten ELSE P.DiscountTB END)*@showDataWrittenCost
						+ (CASE WHEN @BytesToTB*T.TotalDataOnMedia < P.DiscountTB THEN @BytesToTB*T.TotalDataOnMedia ELSE P.DiscountTB END)*@showTotalDataOnMediaCost
						),
				TagDiscountLevel = 1
		FROM	#ClientCopyCost T INNER JOIN #TagPrice P ON T.TagId = P.TagId AND T.TagDiscountLevel = 1 AND P.SizeType = 4
	IF	@i_DiscountLevel = 1
	UPDATE	#ClientCopyCost
	SET		Discount += -0.01*@i_DiscountPct*(
				(CASE WHEN @BytesToTB*FrontEndBackupSize < @i_DiscountTB THEN @BytesToTB*FrontEndBackupSize  ELSE @i_DiscountTB END)*@i_FrontEndBackupTBCost
			  + (CASE WHEN @BytesToTB*FrontEndArchiveSize< @i_DiscountTB THEN @BytesToTB*FrontEndArchiveSize ELSE @i_DiscountTB END)*@i_FrontEndArchiveTBCost
			  +	(CASE WHEN @BytesToTB*PrimaryAppSize     < @i_DiscountTB THEN @BytesToTB*PrimaryAppSize      ELSE @i_DiscountTB END)*@i_PrimaryAppTBCost
			  +	(CASE WHEN @BytesToTB*DataProtected      < @i_DiscountTB THEN @BytesToTB*DataProtected       ELSE @i_DiscountTB END)*@i_DataProtectedTBCost
			  +	(CASE WHEN @BytesToTB*DataWritten        < @i_DiscountTB THEN @BytesToTB*DataWritten         ELSE @i_DiscountTB END)*@i_DataWrittenTBCost
			  +	(CASE WHEN @BytesToTB*TotalProtectedApp  < @i_DiscountTB THEN @BytesToTB*TotalProtectedApp   ELSE @i_DiscountTB END)*@i_TotalProtectedAppTBCost
			  +	(CASE WHEN @BytesToTB*TotalDataOnMedia   < @i_DiscountTB THEN @BytesToTB*TotalDataOnMedia    ELSE @i_DiscountTB END)*@i_TotalDataOnMediaTBCost
			  )
	FROM	#ClientCopyCost
	WHERE	TagDiscountLevel <> 1
	IF @i_AggregateLevel IN (1, 2, 3, 6)
	BEGIN
		INSERT	INTO #ClientCost
		SELECT	CommServUniqueId, ClientId,
				SUM(FrontEndBackupSize), SUM(FrontEndBackupCost),
				SUM(FrontEndArchiveSize),SUM(FrontEndArchiveCost),
				SUM(PrimaryAppSize),     SUM(PrimaryAppCost),
				SUM(DataProtected),      SUM(DataProtectedCost),
				SUM(DataWritten),        SUM(DataWrittenCost),
				SUM(TotalProtectedApp),  SUM(TotalProtectedAppCost),
				SUM(TotalDataOnMedia),   SUM(TotalDataOnMediaCost),
				SUM(nSubclients),
				SUM(BasePrice) + MAX(CASE WHEN TagBasePriceLevel <> 1 AND IsFrontEndCopy = 1 THEN @i_BasePricePerClient ELSE 0 END),
				SUM(Discount)
		FROM	#ClientCopyCost
		GROUP BY CommServUniqueId, ClientId
	END
END
CREATE TABLE #Cost
(
	[Billing Tag] NVARCHAR(256), [Tag Notes<HIDDEN>] NVARCHAR(MAX),
	[CommServ UniqueId<DONOTSHOW>] INT, [CommCell Name] NVARCHAR(256),
	[Tenant] NVARCHAR(256), [Tenant GUID<HIDDEN>] UNIQUEIDENTIFIER,
	[Client Groups] NVARCHAR(MAX), [Client] NVARCHAR(256), [Agent] NVARCHAR(300),
	[Instance] NVARCHAR(256), [Backupset] NVARCHAR(256), [Subclient] NVARCHAR(256),
	[Storage Policy] NVARCHAR(256), [Copy] NVARCHAR(256),
	[Front End Backup Size] DECIMAL(32,3), [Front End Backup Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Front End Archive Size] DECIMAL(32,3), [Front End Archive Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Primary App Size] DECIMAL(32,3), [Primary App Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Protected App Size] DECIMAL(32,3), [Protected App Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Media Size] DECIMAL(32,3), [Media Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Total Protected App Size] DECIMAL(32,3), [Total Protected App Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Total Media Size] DECIMAL(32,3), [Total Media Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Number of Clients<FORMATNUMBERNA>] INT, [Number of Subclients<FORMATNUMBERNA>] INT, [Additional Cost<FORMATNUMBERNA>] DECIMAL(32,3),
	[Discount<FORMATNUMBERNA>] DECIMAL(32,3), [Total Cost<FORMATNUMBERNA>] DECIMAL(32,3)
)
IF @i_AggregateLevel = 5 -- Subclient per Copy
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Tenant]
	ALTER TABLE #Cost DROP COLUMN [Tenant GUID<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
	ALTER TABLE #Cost ADD TagID INT
    ALTER TABLE #Cost ADD [Copy Type<HIDDEN>] VARCHAR(64)
	INSERT	INTO #Cost
	SELECT 	ISNULL(E.Name, ''),
			ISNULL(E.Description, ''),
			R.CommservUniqueId,
			R.CommServName,
			ISNULL(CGS.ClientGroups, ''),
			CASE WHEN A.AppTypeId <> 106 THEN ISNULL(CL.ClientDisplayName, CL.ClientName)
				ELSE ISNULL((SELECT ISNULL(ClientDisplayName, ClientName) FROM cf_SurveyClient WITH (NOLOCK) WHERE CommServUniqueId = S.CommServUniqueId AND ClientId = S.ClientId),
					'VM Client ID: ' + CAST(S.ClientId AS VARCHAR(32))) END AS 'Client',
			A.AgentName + (CASE WHEN A.AppTypeId = 106 THEN (' in ' + A.ClientName) ELSE '' END) AS 'Agent',
			A.InstanceName,
			A.BackupsetName,
			A.SubclientName,
			C.PolicyName,
			C.CopyName,
			@BytesToUnit*S.FrontEndBackupSize,  S.FrontEndBackupCost,
			@BytesToUnit*S.FrontEndArchiveSize,	S.FrontEndArchiveCost,
			@BytesToUnit*S.PrimaryAppSize,      S.PrimaryAppCost,
			@BytesToUnit*S.DataProtected,       S.DataProtectedCost,
			@BytesToUnit*S.DataWritten,         S.DataWrittenCost,
			@BytesToUnit*S.TotalProtectedApp,   S.TotalProtectedAppCost,
			@BytesToUnit*S.TotalDataOnMedia,    S.TotalDataOnMediaCost,
			S.BasePrice,
			0, 0, ISNULL(E.ID, 0),
			CASE WHEN C.IsDefaultCopy = 1 AND C.IsSnapCopy = 0 THEN 'Primary Copy'
			     WHEN C.IsDefaultCopy = 1 AND C.IsSnapCopy = 1 THEN 'Primary Snap Copy'
			     WHEN C.IsDefaultCopy = 0 AND C.IsSnapCopy = 0 AND C.CopyType = 1 THEN 'Synchronous Copy'
			     WHEN C.IsDefaultCopy = 0 AND C.IsSnapCopy = 0 AND C.CopyType = 2 THEN 'Selective Copy'
			     WHEN C.IsDefaultCopy = 0 AND C.IsSnapCopy = 1 AND C.CopyType = 1 THEN 'Synchronous Snap Copy'
			     WHEN C.IsDefaultCopy = 0 AND C.IsSnapCopy = 1 AND C.CopyType = 2 THEN 'Selective Snap Copy'
				 ELSE 'N/A'
			END
	FROM	#SubclientCopyCost S
			INNER JOIN (SELECT DISTINCT CommservUniqueId, CommServName FROM #MonthlyResult) R ON S.CommServUniqueId = R.CommServUniqueId
			INNER JOIN cf_SurveyClient CL WITH (NOLOCK) ON S.CommServUniqueId = CL.CommServUniqueId AND S.ClientId = CL.ClientId
			INNER JOIN cf_SurveySubclientAssociation A WITH (NOLOCK) ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId
			INNER JOIN cf_SurveyCopyBasicRetention C WITH (NOLOCK) ON S.CommServUniqueId = C.CommServUniqueId AND S.CopyId = C.CopyId
			LEFT OUTER JOIN #ClientGroupsString CGS ON S.CommServUniqueId = CGS.CommServUniqueId AND S.ClientId = CGS.ClientId
			LEFT OUTER JOIN #SubclientTags T ON S.CommServUniqueId = T.CommServUniqueId
				AND (S.CopyId = T.CopyId OR T.CopyId = 0 AND S.ClientId = T.ClientId AND S.AppId = T.AppId)
			LEFT OUTER JOIN cf_SurveyBillingEntity E ON T.TagID = E.ID AND E.ID > 0
	WHERE	((ISNULL(@i_ClientGroupName, '') = '' OR CGS.ClientId IS NOT NULL))
	ORDER BY R.CommServName, 'Client', 'Agent', A.InstanceName, A.BackupsetName, A.SubclientName, C.PolicyName, C.CopyName
	IF	@UseTagToClient = 0 AND @UseTagToCopy = 0
	BEGIN
		ALTER TABLE #Cost DROP COLUMN [Tag Notes<HIDDEN>]
		ALTER TABLE #Cost DROP COLUMN [Billing Tag]
	END
	IF	@SubclientDiscountTag = 1
	UPDATE	#Cost
	SET		[Discount<FORMATNUMBERNA>] = -0.01*DiscountPct*Price*(
				(CASE WHEN @BytesToTB*[Front End Backup Size]   < DiscountTB THEN @BytesToTB*[Front End Backup Size]    ELSE DiscountTB END)*@showFrontEndBackupCost
			  + (CASE WHEN @BytesToTB*[Front End Archive Size]  < DiscountTB THEN @BytesToTB*[Front End Archive Size]   ELSE DiscountTB END)*@showFrontEndArchiveCost
			  +	(CASE WHEN @BytesToTB*[Primary App Size]        < DiscountTB THEN @BytesToTB*[Primary App Size]         ELSE DiscountTB END)*@showPrimaryAppCost
			  +	(CASE WHEN @BytesToTB*[Protected App Size]      < DiscountTB THEN @BytesToTB*[Protected App Size]       ELSE DiscountTB END)*@showDataProtectedCost
			  +	(CASE WHEN @BytesToTB*[Media Size]              < DiscountTB THEN @BytesToTB*[Media Size]               ELSE DiscountTB END)*@showDataWrittenCost
			  +	(CASE WHEN @BytesToTB*[Total Protected App Size]< DiscountTB THEN @BytesToTB*[Total Protected App Size] ELSE DiscountTB END)*@showTotalProtectedAppCost
			  +	(CASE WHEN @BytesToTB*[Total Media Size]        < DiscountTB THEN @BytesToTB*[Total Media Size]         ELSE DiscountTB END)*@showTotalDataOnMediaCost
			  )
	FROM	#Cost S
			INNER JOIN #SubclientTags T
			ON S.TagID = T.TagId AND T.DiscountLevel = 0
	IF	@i_DiscountLevel = 0
	UPDATE	#Cost
	SET		[Discount<FORMATNUMBERNA>] = -0.01*@i_DiscountPct*(
				(CASE WHEN @BytesToTB*[Front End Backup Size]   < @i_DiscountTB THEN @BytesToTB*[Front End Backup Size]    ELSE @i_DiscountTB END)*@i_FrontEndBackupTBCost
			  + (CASE WHEN @BytesToTB*[Front End Archive Size]  < @i_DiscountTB THEN @BytesToTB*[Front End Archive Size]   ELSE @i_DiscountTB END)*@i_FrontEndArchiveTBCost
			  +	(CASE WHEN @BytesToTB*[Primary App Size]        < @i_DiscountTB THEN @BytesToTB*[Primary App Size]         ELSE @i_DiscountTB END)*@i_PrimaryAppTBCost
			  +	(CASE WHEN @BytesToTB*[Protected App Size]      < @i_DiscountTB THEN @BytesToTB*[Protected App Size]       ELSE @i_DiscountTB END)*@i_DataProtectedTBCost
			  +	(CASE WHEN @BytesToTB*[Media Size]              < @i_DiscountTB THEN @BytesToTB*[Media Size]               ELSE @i_DiscountTB END)*@i_DataWrittenTBCost
			  +	(CASE WHEN @BytesToTB*[Total Protected App Size]< @i_DiscountTB THEN @BytesToTB*[Total Protected App Size] ELSE @i_DiscountTB END)*@i_TotalProtectedAppTBCost
			  +	(CASE WHEN @BytesToTB*[Total Media Size]        < @i_DiscountTB THEN @BytesToTB*[Total Media Size]         ELSE @i_DiscountTB END)*@i_TotalDataOnMediaTBCost
			  )
	FROM	#Cost
	WHERE	[Discount<FORMATNUMBERNA>] = 0
	IF	@i_BasePricePerSubclient = 0
		SET @ShowBasePrice = 0
	ALTER TABLE #Cost DROP COLUMN TagID
END
ELSE
IF @i_AggregateLevel = 0 -- Subclient
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Tenant]
	ALTER TABLE #Cost DROP COLUMN [Tenant GUID<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Storage Policy]
	ALTER TABLE #Cost DROP COLUMN [Copy]
	ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
	CREATE TABLE #AppCopyTags (CommServUniqueId INT, ClientId INT, AppId INT, TagName NVARCHAR(MAX), TagNotes NVARCHAR(MAX))
	IF @UseTagToCopy = 1
	BEGIN
		;WITH AppCopyTagName (CommServUniqueId, ClientId, AppId, TagName, TagNotes) AS
		(
			SELECT	DISTINCT S.CommServUniqueId, S.ClientId, S.AppId, E.Name, E.Description
			FROM	(SELECT CommServUniqueId, ClientId, AppId
					FROM	#SubclientCopyCost
					WHERE	CopyId > 0 AND TagLevel = 8
					GROUP BY CommServUniqueId, ClientId, AppId
					) A
					INNER JOIN #SubclientCopyCost S ON A.CommServUniqueId = S.CommServUniqueId AND A.ClientId = S.ClientId AND A.AppId = S.AppId
					INNER JOIN cf_SurveyBillingEntity E ON S.TagID = E.ID AND E.ID > 0
		)
		INSERT	INTO #AppCopyTags
		SELECT	CommServUniqueId, ClientId, AppId,
				(SELECT TagName + '; '
				FROM	AppCopyTagName T2
				WHERE	T2.CommServUniqueId = T1.CommServUniqueId AND T2.ClientId = T1.ClientId AND T2.AppId = T1.AppId
				ORDER BY TagName
				FOR XML PATH('')) AS Tags,
				(SELECT TagNotes + '; '
				FROM	AppCopyTagName T3
				WHERE	T3.CommServUniqueId = T1.CommServUniqueId AND T3.ClientId = T1.ClientId AND T3.AppId = T1.AppId
				ORDER BY TagNotes
				FOR XML PATH('')) AS Notes
		FROM	AppCopyTagName T1
		GROUP BY CommServUniqueId, ClientId, AppId
		UPDATE	#AppCopyTags SET TagName = SUBSTRING(TagName, 1, LEN(TagName)-1), TagNotes = SUBSTRING(TagNotes, 1, LEN(TagNotes)-1)
		WHERE	RIGHT(TagName, 2) = '; '
	END
	INSERT	INTO #Cost
	SELECT 	ISNULL(ACT.TagName, ISNULL(E.Name, '')),
			ISNULL(ACT.TagNotes, ISNULL(E.Description, '')),
			R.CommservUniqueId,
			R.CommServName,
			ISNULL(CGS.ClientGroups, ''),
			CASE WHEN A.AppTypeId <> 106 THEN ISNULL(CL.ClientDisplayName, CL.ClientName)
				ELSE ISNULL((SELECT ISNULL(ClientDisplayName, ClientName) FROM cf_SurveyClient WITH (NOLOCK) WHERE CommServUniqueId = S.CommServUniqueId AND ClientId = S.ClientId),
					'VM Client ID: ' + CAST(S.ClientId AS VARCHAR(32))) END AS 'Client',
			A.AgentName + (CASE WHEN A.AppTypeId = 106 THEN (' in ' + A.ClientName) ELSE '' END) AS 'Agent',
			A.InstanceName,
			A.BackupsetName,
			A.SubclientName,
			@BytesToUnit*S.FrontEndBackupSize,  S.FrontEndBackupCost,
			@BytesToUnit*S.FrontEndArchiveSize,	S.FrontEndArchiveCost,
			@BytesToUnit*S.PrimaryAppSize,      S.PrimaryAppCost,
			@BytesToUnit*S.DataProtected,       S.DataProtectedCost,
			@BytesToUnit*S.DataWritten,         S.DataWrittenCost,
			@BytesToUnit*S.TotalProtectedApp,   S.TotalProtectedAppCost,
			@BytesToUnit*S.TotalDataOnMedia,    S.TotalDataOnMediaCost,
			S.BasePrice, S.Discount, 0
	FROM	(SELECT	CommServUniqueId, ClientId, AppId, MAX(TagID) AS TagID,
					SUM(FrontEndBackupSize)    AS FrontEndBackupSize,  SUM(FrontEndBackupCost)    AS FrontEndBackupCost,
					SUM(FrontEndArchiveSize)   AS FrontEndArchiveSize, SUM(FrontEndArchiveCost)   AS FrontEndArchiveCost,
					SUM(PrimaryAppSize)        AS PrimaryAppSize,      SUM(PrimaryAppCost)        AS PrimaryAppCost,
					SUM(DataProtected)         AS DataProtected,       SUM(DataProtectedCost)     AS DataProtectedCost,
					SUM(DataWritten)           AS DataWritten,         SUM(DataWrittenCost)       AS DataWrittenCost,
					SUM(TotalProtectedApp)     AS TotalProtectedApp,   SUM(TotalProtectedAppCost) AS TotalProtectedAppCost,
					SUM(TotalDataOnMedia)      AS TotalDataOnMedia,    SUM(TotalDataOnMediaCost)  AS TotalDataOnMediaCost,
					SUM(BasePrice)             AS BasePrice,           SUM(Discount)              AS Discount
			FROM	#SubclientCopyCost
			GROUP BY CommServUniqueId, ClientId, AppId
			) S
			INNER JOIN (SELECT DISTINCT CommservUniqueId, CommServName FROM #MonthlyResult) R ON S.CommServUniqueId = R.CommServUniqueId
			INNER JOIN cf_SurveyClient CL WITH (NOLOCK) ON S.CommServUniqueId = CL.CommServUniqueId AND S.ClientId = CL.ClientId
			INNER JOIN cf_SurveySubclientAssociation A WITH (NOLOCK) ON S.CommServUniqueId = A.CommServUniqueId AND S.AppId = A.AppId
			LEFT OUTER JOIN #ClientGroupsString CGS ON S.CommServUniqueId = CGS.CommServUniqueId AND S.ClientId = CGS.ClientId
			LEFT OUTER JOIN cf_SurveyBillingEntity E ON S.TagID = E.ID AND E.ID > 0
			LEFT OUTER JOIN #AppCopyTags ACT ON S.CommServUniqueId = ACT.CommServUniqueId AND S.ClientId = ACT.ClientId AND S.AppId = ACT.AppId
	WHERE	(ISNULL(@i_ClientGroupName, '') = '' OR CGS.ClientId IS NOT NULL)
	ORDER BY R.CommServName, 'Client', 'Agent', A.InstanceName, A.BackupsetName, A.SubclientName
	DROP TABLE #AppCopyTags
	IF	@UseTagToClient = 0 AND @UseTagToCopy = 0
	BEGIN
		ALTER TABLE #Cost DROP COLUMN [Tag Notes<HIDDEN>]
		ALTER TABLE #Cost DROP COLUMN [Billing Tag]
	END
	IF	@i_BasePricePerSubclient = 0
		SET @ShowBasePrice = 0
END
ELSE
IF @i_AggregateLevel = 1 -- Client
BEGIN
	CREATE TABLE #ClientTagNames (CommServUniqueId INT, ClientId INT, TagName NVARCHAR(MAX), TagNotes NVARCHAR(MAX))
	IF @UseTagToCopy = 1 OR @UseTagToClient = 1
	BEGIN
		;WITH ClientTagName (CommServUniqueId, ClientId, TagName, TagNotes) AS
		(
			SELECT	DISTINCT S.CommServUniqueId, S.ClientId, E.Name, E.Description
			FROM	(SELECT CommServUniqueId, ClientId
					FROM	#SubclientCopyCost
					GROUP BY CommServUniqueId, ClientId
					) A
					INNER JOIN #SubclientCopyCost S ON A.CommServUniqueId = S.CommServUniqueId AND A.ClientId = S.ClientId
					INNER JOIN cf_SurveyBillingEntity E ON S.TagID = E.ID AND E.ID > 0 AND @i_TagId IN (0, E.ID)
		)
		INSERT	INTO #ClientTagNames
		SELECT	CommServUniqueId, ClientId,
				(SELECT TagName + '; '
				FROM	ClientTagName T2
				WHERE	T2.CommServUniqueId = T1.CommServUniqueId AND T2.ClientId = T1.ClientId
				ORDER BY TagName
				FOR XML PATH('')) AS Tags,
				(SELECT TagNotes + '; '
				FROM	ClientTagName T3
				WHERE	T3.CommServUniqueId = T1.CommServUniqueId AND T3.ClientId = T1.ClientId
				ORDER BY TagNotes
				FOR XML PATH('')) AS Notes
		FROM	ClientTagName T1
		GROUP BY CommServUniqueId, ClientId
		UPDATE	#ClientTagNames SET TagName = SUBSTRING(TagName, 1, LEN(TagName)-1), TagNotes = SUBSTRING(TagNotes, 1, LEN(TagNotes)-1)
		WHERE	RIGHT(TagName, 2) = '; '
	END
	ALTER TABLE #Cost DROP COLUMN [Tenant]
	ALTER TABLE #Cost DROP COLUMN [Tenant GUID<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Agent]
	ALTER TABLE #Cost DROP COLUMN [Instance]
	ALTER TABLE #Cost DROP COLUMN [Backupset]
	ALTER TABLE #Cost DROP COLUMN [Subclient]
	ALTER TABLE #Cost DROP COLUMN [Storage Policy]
	ALTER TABLE #Cost DROP COLUMN [Copy]
	ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	INSERT	INTO #Cost
	SELECT 	ISNULL(TagName, '' ), ISNULL(TagNotes, ''),
			R.CommServUniqueId, R.CommServName, ISNULL(CGS.ClientGroups, ''),
			ISNULL(CL.ClientDisplayName, CL.ClientName),
			@BytesToUnit*S.FrontEndBackupSize,  S.FrontEndBackupCost,
			@BytesToUnit*S.FrontEndArchiveSize,	S.FrontEndArchiveCost,
			@BytesToUnit*S.PrimaryAppSize,      S.PrimaryAppCost,
			@BytesToUnit*S.DataProtected,       S.DataProtectedCost,
			@BytesToUnit*S.DataWritten,         S.DataWrittenCost,
			@BytesToUnit*S.TotalProtectedApp,   S.TotalProtectedAppCost,
			@BytesToUnit*S.TotalDataOnMedia,    S.TotalDataOnMediaCost,
			S.nSubclients, S.BasePrice, S.Discount, 0
	FROM	#ClientCost S
			INNER JOIN (SELECT DISTINCT CommservUniqueId, CommServName FROM #MonthlyResult) R ON S.CommServUniqueId = R.CommServUniqueId
			INNER JOIN cf_SurveyClient CL WITH (NOLOCK) ON S.CommServUniqueId = CL.CommServUniqueId AND S.ClientId = CL.ClientId
			LEFT OUTER JOIN #ClientGroupsString CGS ON S.CommServUniqueId = CGS.CommServUniqueId AND S.ClientId = CGS.ClientId
			LEFT OUTER JOIN #ClientTagNames T ON S.CommServUniqueId = T.CommServUniqueId AND S.ClientId = T.ClientId
	WHERE	(ISNULL(@i_ClientGroupName, '') = '' OR CGS.ClientId IS NOT NULL)
	ORDER BY R.CommServName, ISNULL(CL.ClientDisplayName, CL.ClientName)
	DROP TABLE #ClientTagNames
	IF @UseTagToCopy = 0 AND @UseTagToClient = 0
	BEGIN
		ALTER TABLE #Cost DROP COLUMN [Tag Notes<HIDDEN>]
		ALTER TABLE #Cost DROP COLUMN [Billing Tag]
	END
	IF	@i_BasePricePerSubclient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
END
ELSE
IF @i_AggregateLevel = 2 -- CommCell
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Tenant]
	ALTER TABLE #Cost DROP COLUMN [Tenant GUID<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Client Groups]
	ALTER TABLE #Cost DROP COLUMN [Client]
	ALTER TABLE #Cost DROP COLUMN [Agent]
	ALTER TABLE #Cost DROP COLUMN [Instance]
	ALTER TABLE #Cost DROP COLUMN [Backupset]
	ALTER TABLE #Cost DROP COLUMN [Subclient]
	ALTER TABLE #Cost DROP COLUMN [Storage Policy]
	ALTER TABLE #Cost DROP COLUMN [Copy]
	ALTER TABLE #Cost DROP COLUMN [Tag Notes<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Billing Tag]
	INSERT	INTO #Cost
	SELECT 	C.CommServUniqueId,	C.CommServName,
			@BytesToUnit*S.FrontEndBackupSize,  S.FrontEndBackupCost,
			@BytesToUnit*S.FrontEndArchiveSize,	S.FrontEndArchiveCost,
			@BytesToUnit*S.PrimaryAppSize,      S.PrimaryAppCost,
			@BytesToUnit*S.DataProtected,       S.DataProtectedCost,
			@BytesToUnit*S.DataWritten,         S.DataWrittenCost,
			@BytesToUnit*S.TotalProtectedApp,   S.TotalProtectedAppCost,
			@BytesToUnit*S.TotalDataOnMedia,    S.TotalDataOnMediaCost,
			S.nClients,	S.nSubclients, S.BasePrice, S.Discount, 0
	FROM	(SELECT	CommServUniqueId,
					SUM(FrontEndBackupSize)    AS FrontEndBackupSize,  SUM(FrontEndBackupCost)    AS FrontEndBackupCost,
					SUM(FrontEndArchiveSize)   AS FrontEndArchiveSize, SUM(FrontEndArchiveCost)   AS FrontEndArchiveCost,
					SUM(PrimaryAppSize)        AS PrimaryAppSize,      SUM(PrimaryAppCost)        AS PrimaryAppCost,
					SUM(DataProtected)         AS DataProtected,       SUM(DataProtectedCost)     AS DataProtectedCost,
					SUM(DataWritten)           AS DataWritten,         SUM(DataWrittenCost)       AS DataWrittenCost,
					SUM(TotalProtectedApp)     AS TotalProtectedApp,   SUM(TotalProtectedAppCost) AS TotalProtectedAppCost,
					SUM(TotalDataOnMedia)      AS TotalDataOnMedia,    SUM(TotalDataOnMediaCost)  AS TotalDataOnMediaCost,
					COUNT(*)                   AS nClients,            SUM(nSubclients)           AS nSubclients,
					SUM(BasePrice)             AS BasePrice,           SUM(Discount)              AS Discount
			FROM	#ClientCost
			GROUP BY CommServUniqueId
			) S
			INNER JOIN #MonthlyResult C ON S.CommServUniqueId = C.CommServUniqueId
	ORDER BY C.CommServName
	IF	@i_BasePricePerClient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	IF	@i_BasePricePerSubclient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
END
ELSE
IF @i_AggregateLevel = 3 -- Client Group
BEGIN
	ALTER TABLE #Cost DROP COLUMN [CommServ UniqueId<DONOTSHOW>]
	ALTER TABLE #Cost DROP COLUMN [CommCell Name]
	ALTER TABLE #Cost DROP COLUMN [Tenant]
	ALTER TABLE #Cost DROP COLUMN [Tenant GUID<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Client]
	ALTER TABLE #Cost DROP COLUMN [Agent]
	ALTER TABLE #Cost DROP COLUMN [Instance]
	ALTER TABLE #Cost DROP COLUMN [Backupset]
	ALTER TABLE #Cost DROP COLUMN [Subclient]
	ALTER TABLE #Cost DROP COLUMN [Storage Policy]
	ALTER TABLE #Cost DROP COLUMN [Copy]
	ALTER TABLE #Cost DROP COLUMN [Tag Notes<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Billing Tag]
	INSERT	INTO #Cost
	SELECT 	CG.ClientGroupName,
			@BytesToUnit*S.FrontEndBackupSize,  S.FrontEndBackupCost,
			@BytesToUnit*S.FrontEndArchiveSize,	S.FrontEndArchiveCost,
			@BytesToUnit*S.PrimaryAppSize,      S.PrimaryAppCost,
			@BytesToUnit*S.DataProtected,       S.DataProtectedCost,
			@BytesToUnit*S.DataWritten,         S.DataWrittenCost,
			@BytesToUnit*S.TotalProtectedApp,   S.TotalProtectedAppCost,
			@BytesToUnit*S.TotalDataOnMedia,    S.TotalDataOnMediaCost,
			S.nClients,	S.nSubclients, S.BasePrice, S.Discount, 0
	FROM	(SELECT	C.CommServUniqueId, CGA.ClientGroupId,
					SUM(FrontEndBackupSize)    AS FrontEndBackupSize,  SUM(FrontEndBackupCost)    AS FrontEndBackupCost,
					SUM(FrontEndArchiveSize)   AS FrontEndArchiveSize, SUM(FrontEndArchiveCost)   AS FrontEndArchiveCost,
					SUM(PrimaryAppSize)        AS PrimaryAppSize,      SUM(PrimaryAppCost)        AS PrimaryAppCost,
					SUM(DataProtected)         AS DataProtected,       SUM(DataProtectedCost)     AS DataProtectedCost,
					SUM(DataWritten)           AS DataWritten,         SUM(DataWrittenCost)       AS DataWrittenCost,
					SUM(TotalProtectedApp)     AS TotalProtectedApp,   SUM(TotalProtectedAppCost) AS TotalProtectedAppCost,
					SUM(TotalDataOnMedia)      AS TotalDataOnMedia,    SUM(TotalDataOnMediaCost)  AS TotalDataOnMediaCost,
					COUNT(*)                   AS nClients,            SUM(nSubclients)           AS nSubclients,
					SUM(BasePrice)             AS BasePrice,           SUM(Discount)              AS Discount
			FROM	#ClientCost C
					INNER JOIN cf_SurveyClientGroupAssociation CGA ON C.CommServUniqueId = CGA.CommServUniqueId AND C.ClientId = CGA.ClientId
					INNER JOIN #MonthlyResult R ON CGA.CommservUniqueId = R.CommservUniqueId
			WHERE	R.CGABefore IS NULL AND CGA.DeletedDate IS NULL
				 OR R.CGABefore IS NOT NULL AND (CGA.AddedDate IS NULL OR CGA.AddedDate < R.CGABefore) AND (CGA.DeletedDate IS NULL OR CGA.DeletedDate >= R.CGABefore)
			GROUP BY C.CommServUniqueId, CGA.ClientGroupId
			) S
			INNER JOIN #ClientGroup CG ON S.CommServUniqueId = CG.CommServUniqueId AND S.ClientGroupId = CG.ClientGroupId
	ORDER BY CG.ClientGroupName
	IF	@i_BasePricePerClient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	IF	@i_BasePricePerSubclient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
END
ELSE
IF @i_AggregateLevel = 6 -- Tenant (Client Group)
BEGIN
	ALTER TABLE #Cost DROP COLUMN [CommServ UniqueId<DONOTSHOW>]
	ALTER TABLE #Cost DROP COLUMN [CommCell Name]
	ALTER TABLE #Cost DROP COLUMN [Client Groups]
	ALTER TABLE #Cost DROP COLUMN [Client]
	ALTER TABLE #Cost DROP COLUMN [Agent]
	ALTER TABLE #Cost DROP COLUMN [Instance]
	ALTER TABLE #Cost DROP COLUMN [Backupset]
	ALTER TABLE #Cost DROP COLUMN [Subclient]
	ALTER TABLE #Cost DROP COLUMN [Storage Policy]
	ALTER TABLE #Cost DROP COLUMN [Copy]
	ALTER TABLE #Cost DROP COLUMN [Tag Notes<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Billing Tag]
	INSERT	INTO #Cost
	SELECT 	CG.TenantName, CG.TenantGUID,
			@BytesToUnit*S.FrontEndBackupSize,  S.FrontEndBackupCost,
			@BytesToUnit*S.FrontEndArchiveSize,	S.FrontEndArchiveCost,
			@BytesToUnit*S.PrimaryAppSize,      S.PrimaryAppCost,
			@BytesToUnit*S.DataProtected,       S.DataProtectedCost,
			@BytesToUnit*S.DataWritten,         S.DataWrittenCost,
			@BytesToUnit*S.TotalProtectedApp,   S.TotalProtectedAppCost,
			@BytesToUnit*S.TotalDataOnMedia,    S.TotalDataOnMediaCost,
			S.nClients,	S.nSubclients, S.BasePrice, S.Discount, 0
	FROM	(SELECT	C.CommServUniqueId, CGA.ClientGroupId,
					SUM(FrontEndBackupSize)    AS FrontEndBackupSize,  SUM(FrontEndBackupCost)    AS FrontEndBackupCost,
					SUM(FrontEndArchiveSize)   AS FrontEndArchiveSize, SUM(FrontEndArchiveCost)   AS FrontEndArchiveCost,
					SUM(PrimaryAppSize)        AS PrimaryAppSize,      SUM(PrimaryAppCost)        AS PrimaryAppCost,
					SUM(DataProtected)         AS DataProtected,       SUM(DataProtectedCost)     AS DataProtectedCost,
					SUM(DataWritten)           AS DataWritten,         SUM(DataWrittenCost)       AS DataWrittenCost,
					SUM(TotalProtectedApp)     AS TotalProtectedApp,   SUM(TotalProtectedAppCost) AS TotalProtectedAppCost,
					SUM(TotalDataOnMedia)      AS TotalDataOnMedia,    SUM(TotalDataOnMediaCost)  AS TotalDataOnMediaCost,
					COUNT(*)                   AS nClients,            SUM(nSubclients)           AS nSubclients,
					SUM(BasePrice)             AS BasePrice,           SUM(Discount)              AS Discount
			FROM	#ClientCost C
					INNER JOIN cf_SurveyClientGroupAssociation CGA ON C.CommServUniqueId = CGA.CommServUniqueId AND C.ClientId = CGA.ClientId
					INNER JOIN #MonthlyResult R ON CGA.CommservUniqueId = R.CommservUniqueId
			WHERE	R.CGABefore IS NULL AND CGA.DeletedDate IS NULL
				 OR R.CGABefore IS NOT NULL AND (CGA.AddedDate IS NULL OR CGA.AddedDate < R.CGABefore) AND (CGA.DeletedDate IS NULL OR CGA.DeletedDate >= R.CGABefore)
			GROUP BY C.CommServUniqueId, CGA.ClientGroupId
			) S
			INNER JOIN #ClientGroup CG ON S.CommServUniqueId = CG.CommServUniqueId AND S.ClientGroupId = CG.ClientGroupId
	ORDER BY CG.TenantName
	IF	@i_BasePricePerClient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	IF	@i_BasePricePerSubclient = 0
		ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
END
ELSE
IF @i_AggregateLevel = 4 -- Billing Tag
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Tenant]
	ALTER TABLE #Cost DROP COLUMN [Tenant GUID<HIDDEN>]
	ALTER TABLE #Cost DROP COLUMN [Number of Clients<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Number of Subclients<FORMATNUMBERNA>]
	CREATE TABLE #CommServClientName (CommServUniqueId INT, CommServName NVARCHAR(256), ClientId INT, ClientName NVARCHAR(256))
	INSERT	INTO #CommServClientName
	SELECT	R.CommServUniqueId, R.CommServName, CL.ClientId, ISNULL(CL.ClientDisplayName, CL.ClientName)
	FROM	#MonthlyResult R INNER JOIN cf_SurveyClient CL WITH (NOLOCK) ON R.CommServUniqueId = CL.CommServUniqueId AND R.TagToCopy = 0
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, R.CommservUniqueId, R.CommServName, '', '', '', '', '', '', C.PolicyName, C.CopyName,
			@BytesToUnit*U.FrontEndBackupSize,  U.FrontEndBackupCost,
			@BytesToUnit*U.FrontEndArchiveSize,	U.FrontEndArchiveCost,
			@BytesToUnit*U.PrimaryAppSize,      U.PrimaryAppCost,
			@BytesToUnit*U.DataProtected,       U.DataProtectedCost,
			@BytesToUnit*U.DataWritten,         U.DataWrittenCost,
			@BytesToUnit*U.TotalProtectedApp,   U.TotalProtectedAppCost,
			@BytesToUnit*U.TotalDataOnMedia,    U.TotalDataOnMediaCost,
			U.BasePrice, U.Discount, 0
	FROM	(SELECT	CommServUniqueId, CopyId,
					SUM(FrontEndBackupSize)    AS FrontEndBackupSize,  SUM(FrontEndBackupCost)    AS FrontEndBackupCost,
					SUM(FrontEndArchiveSize)   AS FrontEndArchiveSize, SUM(FrontEndArchiveCost)   AS FrontEndArchiveCost,
					SUM(PrimaryAppSize)        AS PrimaryAppSize,      SUM(PrimaryAppCost)        AS PrimaryAppCost,
					SUM(DataProtected)         AS DataProtected,       SUM(DataProtectedCost)     AS DataProtectedCost,
					SUM(DataWritten)           AS DataWritten,         SUM(DataWrittenCost)       AS DataWrittenCost,
					SUM(TotalProtectedApp)     AS TotalProtectedApp,   SUM(TotalProtectedAppCost) AS TotalProtectedAppCost,
					SUM(TotalDataOnMedia)      AS TotalDataOnMedia,    SUM(TotalDataOnMediaCost)  AS TotalDataOnMediaCost,
					SUM(BasePrice)             AS BasePrice,           SUM(Discount)              AS Discount
			FROM	#ClientCopyCost
			WHERE	CopyId > 0 AND TagLevel = 8
			GROUP BY CommServUniqueId, CopyId
			) U
			INNER JOIN (SELECT DISTINCT CommServUniqueId, CommServName FROM #MonthlyResult WHERE TagToCopy = 1) R ON U.CommServUniqueId = R.CommServUniqueId
			INNER JOIN cf_SurveyCopyBasicRetention C ON U.CommServUniqueId = C.CommServUniqueId AND U.CopyId = C.CopyId
			INNER JOIN cf_SurveyBillingEntityToCopy EC ON U.CommServUniqueId = EC.CommServUniqueId AND U.CopyId = EC.CopyId
			INNER JOIN cf_SurveyBillingEntity E ON EC.EntityID = E.ID AND @i_TagId IN (0, E.ID)
	ORDER BY E.Name, E.Description, C.PolicyName, C.CopyName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, R.CommservUniqueId, R.CommServName, '', '', '', '', '', '', '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#ClientCopyCost AS U
			INNER JOIN (SELECT DISTINCT CommServUniqueId, CommServName FROM #MonthlyResult WHERE TagToCopy = 0) R ON U.CommServUniqueId = R.CommServUniqueId AND U.TagLevel = 7
			INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND EM.ClientGroupId = 0 AND EM.ClientId = 0
	GROUP BY E.Name, E.Description, R.CommservUniqueId, R.CommServName
	ORDER BY E.Name, R.CommServName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, R.CommservUniqueId, R.CommServName, CG.ClientGroupName, '', '', '', '', '', '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#ClientCopyCost AS U
			INNER JOIN (SELECT DISTINCT CommServUniqueId, CommServName, CGABefore FROM #MonthlyResult WHERE TagToCopy = 0) R ON U.CommServUniqueId = R.CommServUniqueId AND U.TagLevel = 6
			INNER JOIN cf_SurveyClientGroupAssociation CGA ON U.CommServUniqueId = CGA.CommServUniqueId AND U.ClientId = CGA.ClientId
			INNER JOIN cf_SurveyClientGroup CG ON CGA.CommServUniqueId = CG.CommServUniqueId AND CGA.ClientGroupId = CG.ClientGroupId
            INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND CGA.ClientGroupId = EM.ClientGroupId AND EM.ClientId = 0
	WHERE	EM.ClientGroupId <= 0 OR CG.ClientGroupId = EM.ClientGroupId
		AND (R.CGABefore IS NULL AND CGA.DeletedDate IS NULL
		  OR R.CGABefore IS NOT NULL AND (CGA.AddedDate IS NULL OR CGA.AddedDate < R.CGABefore) AND (CGA.DeletedDate IS NULL OR CGA.DeletedDate >= R.CGABefore))
	GROUP BY E.Name, E.Description, R.CommservUniqueId, R.CommServName, CG.ClientGroupName
	ORDER BY E.Name, R.CommServName, CG.ClientGroupName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, S.CommservUniqueId, S.CommServName, '', S.ClientName, '', '', '', '', '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#ClientCopyCost AS U
			INNER JOIN #CommServClientName S ON U.CommServUniqueId = S.CommServUniqueId AND U.ClientId = S.ClientId AND U.TagLevel = 5
			INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND U.ClientId = EM.ClientId AND EM.AppTypeId = 0
	GROUP BY E.Name, E.Description, S.CommservUniqueId, S.CommServName, S.ClientName
	ORDER BY E.Name, S.CommServName, S.ClientName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, S.CommservUniqueId, S.CommServName, '', S.ClientName, A.AgentName, '', '', '', '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#SubclientCopyCost AS U
			INNER JOIN #CommServClientName S ON U.CommServUniqueId = S.CommServUniqueId AND U.ClientId = S.ClientId AND U.TagLevel = 4
			INNER JOIN cf_SurveySubclientAssociation A ON U.CommServUniqueId = A.CommServUniqueId AND U.AppId = A.AppId
			INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND U.ClientId = EM.ClientId AND A.AppTypeId = EM.AppTypeId AND EM.InstanceId = 0
	GROUP BY E.Name, E.Description, S.CommservUniqueId, S.CommServName, S.ClientName, A.AgentName
	ORDER BY E.Name, S.CommServName, S.ClientName, A.AgentName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, S.CommservUniqueId, S.CommServName, '', S.ClientName, A.AgentName, A.InstanceName, '', '', '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#SubclientCopyCost AS U
			INNER JOIN #CommServClientName S ON U.CommServUniqueId = S.CommServUniqueId AND U.ClientId = S.ClientId AND U.TagLevel = 3
			INNER JOIN cf_SurveySubclientAssociation A ON U.CommServUniqueId = A.CommServUniqueId AND U.AppId = A.AppId
			INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND U.ClientId = EM.ClientId AND A.AppTypeId = EM.AppTypeId
                AND A.InstanceId = EM.InstanceId AND EM.BackupsetId= 0
	GROUP BY E.Name, E.Description, S.CommservUniqueId, S.CommServName, S.ClientName, A.AgentName, A.InstanceName
	ORDER BY E.Name, S.CommServName, S.ClientName, A.AgentName, A.InstanceName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, S.CommservUniqueId, S.CommServName, '', S.ClientName, A.AgentName, A.InstanceName, A.BackupsetName, '', '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#SubclientCopyCost AS U
			INNER JOIN #CommServClientName S ON U.CommServUniqueId = S.CommServUniqueId AND U.ClientId = S.ClientId AND U.TagLevel = 2
			INNER JOIN cf_SurveySubclientAssociation A ON U.CommServUniqueId = A.CommServUniqueId AND U.ClientId = A.ClientId AND U.AppId = A.AppId
			INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND U.ClientId = EM.ClientId AND A.AppTypeId = EM.AppTypeId
                AND A.InstanceId = EM.InstanceId AND A.BackupSetId = EM.BackupSetId AND EM.AppId = 0
	GROUP BY E.Name, E.Description, S.CommservUniqueId, S.CommServName, S.ClientName, A.AgentName, A.InstanceName, A.BackupsetName
	ORDER BY E.Name, S.CommServName, S.ClientName, A.AgentName, A.InstanceName, A.BackupsetName
	INSERT	INTO #Cost
	SELECT 	E.Name, E.Description, S.CommservUniqueId, S.CommServName, '', S.ClientName, A.AgentName, A.InstanceName, A.BackupsetName, A.SubclientName, '', '',
			@BytesToUnit*SUM(U.FrontEndBackupSize),  SUM(U.FrontEndBackupCost),
			@BytesToUnit*SUM(U.FrontEndArchiveSize), SUM(U.FrontEndArchiveCost),
			@BytesToUnit*SUM(U.PrimaryAppSize),      SUM(U.PrimaryAppCost),
			@BytesToUnit*SUM(U.DataProtected),       SUM(U.DataProtectedCost),
			@BytesToUnit*SUM(U.DataWritten),         SUM(U.DataWrittenCost),
			@BytesToUnit*SUM(U.TotalProtectedApp),   SUM(U.TotalProtectedAppCost),
			@BytesToUnit*SUM(U.TotalDataOnMedia),    SUM(U.TotalDataOnMediaCost),
			SUM(U.BasePrice), SUM(U.Discount), 0
	FROM	#SubclientCopyCost AS U
			INNER JOIN #CommServClientName S ON U.CommServUniqueId = S.CommServUniqueId AND U.ClientId = S.ClientId AND U.TagLevel = 1
			INNER JOIN cf_SurveySubclientAssociation A ON U.CommServUniqueId = A.CommServUniqueId AND U.ClientId = A.ClientId AND U.AppId = A.AppId
			INNER JOIN cf_SurveyBillingEntity E ON U.TagId = E.ID AND @i_TagId IN (0, E.ID)
            INNER JOIN cf_SurveyBillingEntityMember EM ON E.ID = EM.EntityID AND U.ClientId = EM.ClientId AND U.AppId = EM.AppId
	GROUP BY E.Name, E.Description, S.CommservUniqueId, S.CommServName, S.ClientName, A.AgentName, A.InstanceName, A.BackupsetName, A.SubclientName
	ORDER BY E.Name, S.CommServName, S.ClientName, A.AgentName, A.InstanceName, A.BackupsetName, A.SubclientName
	DROP TABLE #CommServClientName
	IF @i_TagId = 0
	BEGIN
		CREATE TABLE #TagCost
		(
			[Billing Tag] NVARCHAR(256), [Tag Notes<HIDDEN>] NVARCHAR(MAX),
			[Front End Backup Size] DECIMAL(32,3), [Front End Backup Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Front End Archive Size] DECIMAL(32,3), [Front End Archive Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Primary App Size] DECIMAL(32,3), [Primary App Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Protected App Size] DECIMAL(32,3), [Protected App Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Media Size] DECIMAL(32,3), [Media Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Total Protected App Size] DECIMAL(32,3), [Total Protected App Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Total Media Size] DECIMAL(32,3), [Total Media Cost<FORMATNUMBERNA>] DECIMAL(32,3),
			[Additional Cost<FORMATNUMBERNA>] DECIMAL(32,3), [Discount<FORMATNUMBERNA>] DECIMAL(32,3), [Total Cost<FORMATNUMBERNA>] DECIMAL(32,3)
		)
		INSERT	INTO #TagCost
		SELECT	[Billing Tag], [Tag Notes<HIDDEN>],
				SUM([Front End Backup Size]), SUM([Front End Backup Cost<FORMATNUMBERNA>]),
				SUM([Front End Archive Size]), SUM([Front End Archive Cost<FORMATNUMBERNA>]),
				SUM([Primary App Size]), SUM([Primary App Cost<FORMATNUMBERNA>]),
				SUM([Protected App Size]), SUM([Protected App Cost<FORMATNUMBERNA>]),
				SUM([Media Size]), SUM([Media Cost<FORMATNUMBERNA>]),
				SUM([Total Protected App Size]), SUM([Total Protected App Cost<FORMATNUMBERNA>]),
				SUM([Total Media Size]), SUM([Total Media Cost<FORMATNUMBERNA>]),
				SUM([Additional Cost<FORMATNUMBERNA>]), SUM([Discount<FORMATNUMBERNA>]), SUM([Total Cost<FORMATNUMBERNA>])
		FROM	#Cost
		GROUP BY [Billing Tag], [Tag Notes<HIDDEN>]
		DELETE FROM #Cost
		ALTER TABLE #Cost DROP COLUMN [CommServ UniqueId<DONOTSHOW>]
		ALTER TABLE #Cost DROP COLUMN [CommCell Name]
		ALTER TABLE #Cost DROP COLUMN [Client Groups]
		ALTER TABLE #Cost DROP COLUMN [Client]
		ALTER TABLE #Cost DROP COLUMN [Agent]
		ALTER TABLE #Cost DROP COLUMN [Instance]
		ALTER TABLE #Cost DROP COLUMN [Backupset]
		ALTER TABLE #Cost DROP COLUMN [Subclient]
		ALTER TABLE #Cost DROP COLUMN [Storage Policy]
		ALTER TABLE #Cost DROP COLUMN [Copy]
		INSERT	INTO #Cost
		SELECT * FROM #TagCost
		DROP TABLE #TagCost
	END
	ELSE
	BEGIN
		IF NOT EXISTS (SELECT * FROM #MonthlyResult WHERE TagToCopy = 0)
		BEGIN
			ALTER TABLE #Cost DROP COLUMN [Client Groups]
			ALTER TABLE #Cost DROP COLUMN [Client]
			ALTER TABLE #Cost DROP COLUMN [Agent]
			ALTER TABLE #Cost DROP COLUMN [Instance]
			ALTER TABLE #Cost DROP COLUMN [Backupset]
			ALTER TABLE #Cost DROP COLUMN [Subclient]
		END
		IF NOT EXISTS (SELECT * FROM #MonthlyResult WHERE TagToCopy = 1)
		BEGIN
			ALTER TABLE #Cost DROP COLUMN [Storage Policy]
			ALTER TABLE #Cost DROP COLUMN [Copy]
		END
	END
END
IF OBJECT_ID('tempdb.dbo.#MonthlyResult') IS NOT NULL DROP TABLE #MonthlyResult
IF OBJECT_ID('tempdb.dbo.#FrontEndCopy') IS NOT NULL DROP TABLE #FrontEndCopy
IF OBJECT_ID('tempdb.dbo.#ClientGroupsString') IS NOT NULL DROP TABLE #ClientGroupsString
IF OBJECT_ID('tempdb.dbo.#SubclientTags') IS NOT NULL DROP TABLE #SubclientTags
IF OBJECT_ID('tempdb.dbo.#SubclientCopyCost') IS NOT NULL DROP TABLE #SubclientCopyCost
IF OBJECT_ID('tempdb.dbo.#ClientCopyCost') IS NOT NULL DROP TABLE #ClientCopyCost
IF OBJECT_ID('tempdb.dbo.#ClientCost') IS NOT NULL DROP TABLE #ClientCost
IF OBJECT_ID('tempdb.dbo.#TagPrice') IS NOT NULL DROP TABLE #TagPrice
IF	@ShowTotalCost > 0
BEGIN
	UPDATE #Cost SET [Total Cost<FORMATNUMBERNA>] =
		  CASE WHEN @i_FrontEndBackupTBCost = 0    THEN 0 ELSE [Front End Backup Cost<FORMATNUMBERNA>] END
		+ CASE WHEN @i_FrontEndArchiveTBCost = 0   THEN 0 ELSE [Front End Archive Cost<FORMATNUMBERNA>] END
		+ CASE WHEN @i_PrimaryAppTBCost = 0        THEN 0 ELSE [Primary App Cost<FORMATNUMBERNA>] END
		+ CASE WHEN @i_DataProtectedTBCost = 0     THEN 0 ELSE [Protected App Cost<FORMATNUMBERNA>] END
		+ CASE WHEN @i_DataWrittenTBCost = 0       THEN 0 ELSE [Media Cost<FORMATNUMBERNA>] END
		+ CASE WHEN @i_TotalProtectedAppTBCost = 0 THEN 0 ELSE [Total Protected App Cost<FORMATNUMBERNA>] END
		+ CASE WHEN @i_TotalDataOnMediaTBCost = 0  THEN 0 ELSE [Total Media Cost<FORMATNUMBERNA>] END
		+ [Additional Cost<FORMATNUMBERNA>]
		+ [Discount<FORMATNUMBERNA>]
	UPDATE #Cost SET [Total Cost<FORMATNUMBERNA>] = 0 WHERE [Total Cost<FORMATNUMBERNA>] < 0
END
IF @i_Options&4 > 0 -- Show a row of Total
BEGIN
	INSERT INTO #Cost ([Front End Backup Size], [Front End Backup Cost<FORMATNUMBERNA>],
			[Front End Archive Size], [Front End Archive Cost<FORMATNUMBERNA>],
			[Primary App Size], [Primary App Cost<FORMATNUMBERNA>],
			[Protected App Size], [Protected App Cost<FORMATNUMBERNA>],
			[Media Size], [Media Cost<FORMATNUMBERNA>],
			[Total Protected App Size], [Total Protected App Cost<FORMATNUMBERNA>],
			[Total Media Size], [Total Media Cost<FORMATNUMBERNA>],
			[Additional Cost<FORMATNUMBERNA>], [Discount<FORMATNUMBERNA>], [Total Cost<FORMATNUMBERNA>])
	SELECT	SUM([Front End Backup Size]), SUM([Front End Backup Cost<FORMATNUMBERNA>]),
			SUM([Front End Archive Size]), SUM([Front End Archive Cost<FORMATNUMBERNA>]),
			SUM([Primary App Size]), SUM([Primary App Cost<FORMATNUMBERNA>]),
			SUM([Protected App Size]), SUM([Protected App Cost<FORMATNUMBERNA>]),
			SUM([Media Size]), SUM([Media Cost<FORMATNUMBERNA>]),
			SUM([Total Protected App Size]), SUM([Total Protected App Cost<FORMATNUMBERNA>]),
			SUM([Total Media Size]), SUM([Total Media Cost<FORMATNUMBERNA>]),
			SUM([Additional Cost<FORMATNUMBERNA>]), SUM([Discount<FORMATNUMBERNA>]), SUM([Total Cost<FORMATNUMBERNA>])
	FROM	#Cost
	IF (@UseTagToClient > 0 OR @UseTagToCopy > 0)
	BEGIN
		IF @i_AggregateLevel = 0
			UPDATE	#Cost SET [Billing Tag] = '', [Tag Notes<HIDDEN>] = ''
			WHERE	[Subclient] IS NULL
		ELSE IF @i_AggregateLevel = 1
			UPDATE	#Cost SET [Billing Tag] = '', [Tag Notes<HIDDEN>] = ''
			WHERE	[Client] IS NULL
		ELSE IF @i_AggregateLevel = 5
			UPDATE	#Cost SET [Billing Tag] = '', [Tag Notes<HIDDEN>] = ''
			WHERE	[Copy] IS NULL
	END
	IF @i_AggregateLevel = 0
		UPDATE	#Cost SET [CommServ UniqueId<DONOTSHOW>] = -1, [CommCell Name] = '', [Client Groups] = '',
				[Client] = '', [Agent] = '', [Instance] = '', [Backupset] = '', [Subclient] = 'Total of All Rows'
		WHERE	[Subclient] IS NULL
	ELSE IF @i_AggregateLevel = 1
		UPDATE	#Cost SET [CommServ UniqueId<DONOTSHOW>] = -1, [CommCell Name] = '', [Client Groups] = '',
				[Client] = 'Total of All Rows'
		WHERE	[Client] IS NULL
	ELSE IF @i_AggregateLevel = 2
		UPDATE	#Cost SET [CommServ UniqueId<DONOTSHOW>] = -1, [CommCell Name] = 'Total of All Rows'
		WHERE	[CommCell Name] IS NULL
	ELSE IF @i_AggregateLevel = 3
		UPDATE	#Cost SET [Client Groups] = 'Total of All Rows'
		WHERE	[Client Groups] IS NULL
	ELSE IF @i_AggregateLevel = 4
		UPDATE	#Cost SET [Billing Tag] = 'Total of All Rows', [Tag Notes<HIDDEN>] = ''
		WHERE	[Billing Tag] IS NULL
	ELSE IF @i_AggregateLevel = 5
		UPDATE	#Cost SET [CommServ UniqueId<DONOTSHOW>] = -1, [CommCell Name] = '', [Client Groups] = '',
				[Client] = '', [Agent] = '', [Instance] = '', [Backupset] = '', [Subclient] = '',
				[Storage Policy] = '', [Copy] = 'Total of All Rows'
		WHERE	[Copy] IS NULL
	ELSE IF @i_AggregateLevel = 6
		UPDATE	#Cost SET [Tenant] = 'Total of All Rows'
		WHERE	[Tenant] IS NULL
END
IF	@ShowTotalCost = 0
	ALTER TABLE #Cost DROP COLUMN [Total Cost<FORMATNUMBERNA>]
IF	@ShowDiscount = 0
	ALTER TABLE #Cost DROP COLUMN [Discount<FORMATNUMBERNA>]
IF	@ShowBasePrice = 0
	ALTER TABLE #Cost DROP COLUMN [Additional Cost<FORMATNUMBERNA>]
IF	@i_TotalDataOnMediaTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Total Media Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Total Media Size]
END
IF	@i_TotalProtectedAppTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Total Protected App Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Total Protected App Size]
END
IF	@i_DataWrittenTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Media Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Media Size]
END
IF	@i_DataProtectedTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Protected App Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Protected App Size]
END
IF	@i_PrimaryAppTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Primary App Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Primary App Size]
END
IF	@i_FrontEndBackupTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Front End Backup Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Front End Backup Size]
END
IF	@i_FrontEndArchiveTBCost = 0
BEGIN
	ALTER TABLE #Cost DROP COLUMN [Front End Archive Cost<FORMATNUMBERNA>]
	ALTER TABLE #Cost DROP COLUMN [Front End Archive Size]
END
IF @i_Options&4 > 0 -- Show a row of Total
BEGIN
	IF @i_AggregateLevel = 0
		SELECT * FROM #Cost WHERE [Subclient] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [Subclient] <> 'Total of All Rows'
	ELSE IF @i_AggregateLevel = 1
		SELECT * FROM #Cost WHERE [Client] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [Client] <> 'Total of All Rows'
	ELSE IF @i_AggregateLevel = 2
		SELECT * FROM #Cost WHERE [CommCell Name] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [CommCell Name] <> 'Total of All Rows'
	ELSE IF @i_AggregateLevel = 3
		SELECT * FROM #Cost WHERE [Client Groups] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [Client Groups] <> 'Total of All Rows'
	ELSE IF @i_AggregateLevel = 4
		SELECT * FROM #Cost WHERE [Billing Tag] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [Billing Tag] <> 'Total of All Rows'
	ELSE IF @i_AggregateLevel = 5
		SELECT * FROM #Cost WHERE [Copy] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [Copy] <> 'Total of All Rows'
	ELSE IF @i_AggregateLevel = 6
		SELECT * FROM #Cost WHERE [Tenant] = 'Total of All Rows'
		UNION ALL
		SELECT * FROM #Cost WHERE [Tenant] <> 'Total of All Rows'
END
ELSE
	SELECT * FROM #Cost
DROP TABLE #Cost
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'RptMonthlyStorageUsage',  'v1.24.2.40.4.1', 'RptMonthlyStorageUsage', 'v1.24.2.40.4.1')
GO

