--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 61 -- ddb configuration
DECLARE @surveyXML NVARCHAR(MAX)

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



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

--------- Insert your SQL statements here 
--DDB partitions on MediaAgents

DECLARE @nreleaseId INT =1
DECLARE @nstring NVARCHAR(MAX)
DECLARE @DedupStoreConfig XML
SET @nreleaseId =(SELECT releaseId from APP_Client  where id =2)
IF object_id('tempdb.dbo.#tmpDedupStoreConfig') is not null DROP TABLE #tmpDedupStoreConfig				
	CREATE TABLE #tmpDedupStoreConfig
(
	clientId INTEGER,
	SIDBStoreId INTEGER,
	SubStoreId INTEGER,
	idxAccessPathId INTEGER,
	SIDBStoreAliasName VARCHAR(MAX),
	accessPath VARCHAR(MAX),
	MACount INTEGER,
	SIDBCountForMA	INTEGER,
	SubstoreCountForMA INTEGER,
	SubstoreCountForStore INTEGER,
	SealedStatus varchar(16),
	SIDBStoreStatus INTEGER,
	TotalDataSizeMB BIGINT,
	SealedTime INTEGER, 
	SealedReason INTEGER, 
	TotalRecords BIGINT,
	DDBDiskTotalSpaceMB BIGINT,
	DDBDiskFreeSpaceMB BIGINT,
	DDBUsedDiskSpaceMB BIGINT	
	)

-- Get number of SIDB stores from IdxSIDBStore table
SET @nstring =N''

IF(@nreleaseId =14)
BEGIN
 SET @nstring =@nstring +N'

	INSERT INTO #tmpDedupStoreConfig
	select distinct a.ClientId, a.SIDBStoreId, 0, a.idxAccessPathId, a.sidbstorename, b.path, 0, 0, 0, 0, 
		(case when a.sealedtime > 0 then ''Sealed'' else ''Active'' end), a.Status, (a.TotalDataSizeBytes/1024/1024), a.SealedTime, a.SealedReason, 0,
		c.TotalCapacityMB, c.FreeDiskSpaceMB, c.DiskUsageMB
	from IdxSIDBStore a, idxAccessPath b, IdxCache c
	where a.SIDBStoreId > 0
	and a.idxAccessPathId = b.idxAccessPathId and a.IdxCacheId = c.IdxCacheId
	and a.firstbackuptime > 0
	and a.ClientId > 1 
	and a.SIDBStoreId = CASE WHEN a.origCCSIDBStoreId > 0 THEN a.origCCSIDBStoreId ELSE a.SIDBStoreId END
	and a.commCellId = 2

	DECLARE @totalMA INTEGER = 0
	SET @totalMA = (SELECT COUNT(DISTINCT clientId) from #tmpDedupStoreConfig)

	update #tmpDedupStoreConfig set MACount = @totalMA

	update #tmpDedupStoreConfig
	SET SIDBCountForMA = (
		SELECT COUNT(distinct SIDBStoreId) from #tmpDedupStoreConfig B
    	WHERE A.clientId = B.ClientId
		GROUP BY B.ClientId
		)
	FROM #tmpDedupStoreConfig A
	
	update #tmpDedupStoreConfig
	SET SubstoreCountForMA = SIDBCountForMA

	update #tmpDedupStoreConfig
	SET SubstoreCountForStore = 1
	
	update #tmpDedupStoreConfig --lpulickal,10/18/2013
	SET TotalRecords = 
	(
		SELECT SUM(PrimaryEntries) from IdxSIDBUsageHistory2 B
    	WHERE A.SIDBStoreId = B.SIDBStoreId 
		GROUP BY B.SIDBStoreId
	)
	FROM #tmpDedupStoreConfig A
	
	'
	
	
END		
ELSE IF(@nreleaseId >=15)
BEGIN
SET @nstring = @nstring +N'
	INSERT INTO #tmpDedupStoreConfig
	select distinct b.ClientId, b.SIDBStoreId, b.SubStoreId, b.idxAccessPathId, a.sidbstorename, c.path, 0, 0, 0, 0, 
		(case when a.sealedtime > 0 then ''Sealed'' else ''Active'' end), b.Status, (b.TotalDataSizeBytes/1024/1024), a.SealedTime, a.SealedReason, 0,
		d.TotalCapacityMB, d.FreeDiskSpaceMB, d.DiskUsageMB
	from IdxSIDBStore a, IdxSIDBSubStore b, idxAccessPath c, IdxCache d
	where a.SIDBStoreId > 0
	and a.SIDBStoreId = b.SIDBStoreId and b.idxAccessPathId = c.idxAccessPathId and b.IdxCacheId = d.IdxCacheId
	and a.firstbackuptime > 0
	and b.ClientId > 1 
	and b.SubStoreId = CASE WHEN b.origSubStoreId > 0 THEN b.origSubStoreId ELSE b.SubStoreId END
	and b.commCellId = 2
	
	DECLARE @totalMA INTEGER = 0
	SET @totalMA = (SELECT COUNT(DISTINCT clientId) from #tmpDedupStoreConfig)

	update #tmpDedupStoreConfig set MACount = @totalMA

	update #tmpDedupStoreConfig
	SET SIDBCountForMA = (
		SELECT COUNT(distinct SIDBStoreId) from #tmpDedupStoreConfig B
    	WHERE A.clientId = B.ClientId
		GROUP BY B.ClientId
		)
	FROM #tmpDedupStoreConfig A
	
	update #tmpDedupStoreConfig
	SET SubstoreCountForMA = (
		SELECT COUNT(distinct SubStoreId) from #tmpDedupStoreConfig B
    	WHERE A.clientId = B.ClientId
		GROUP BY B.ClientId
		)
	FROM #tmpDedupStoreConfig A

	update #tmpDedupStoreConfig
	SET SubstoreCountForStore = (
		SELECT COUNT(distinct SubStoreId) from #tmpDedupStoreConfig B
    	WHERE A.SIDBStoreId = B.SIDBStoreId
		GROUP BY B.SIDBStoreId
		)
	FROM #tmpDedupStoreConfig A
	
	update #tmpDedupStoreConfig --lpulickal,10/18/2013
	SET TotalRecords = 
	(
		SELECT SUM(PrimaryEntries) from IdxSIDBUsageHistory B
    	WHERE A.SubStoreId = B.SubStoreId 
		GROUP BY B.SubStoreId
	)
	FROM #tmpDedupStoreConfig A
	'		
END		
exec sp_executesql @nstring

SET @DedupStoreConfig = (
		-- Include your select columns here
			SELECT 
					clientId AS '@clientId',
					SIDBStoreId AS '@SIDBStoreId',
					SubStoreId AS '@SubstoreId',
					idxAccessPathId AS '@idxAccessPathId',
					SIDBStoreAliasName AS '@SIDBStoreAliasName',
					dbo.NormalizeForXML(c.name) AS '@clientName',
					accessPath AS '@AccessPath',
					MACount AS '@MACount',
					SIDBCountForMA AS '@SIDBCountForMA',
					SubstoreCountForMA AS '@SubstoreCountForMA',
					SubstoreCountForStore AS '@SubstoreCountForStore',
					SealedStatus AS '@SealedStatus',
					SIDBStoreStatus AS '@SIDBStoreStatus',
					TotalDataSizeMB AS '@TotalDataSizeMB',
					SealedTime AS '@SealedTime',
					TotalRecords AS '@TotalRecords',
					SealedReason AS '@SealedReason',
					DDBDiskTotalSpaceMB AS '@DDBDiskTotalSpaceMB',
					DDBDiskFreeSpaceMB AS '@DDBDiskFreeSpaceMB',
					DDBUsedDiskSpaceMB AS '@DDBUsedDiskSpaceMB'
			FROM #tmpDedupStoreConfig s
				INNER JOIN APP_Client c ON s.clientId = c.id
		FOR XML PATH ('DedupStoreConfig') )
		
DROP TABLE #tmpDedupStoreConfig


SET	@surveyXML = (SELECT @DedupStoreConfig FOR XML PATH ('DedupStoreConfigXML'))
 
-- Replace <<RESULTS_NAME>> with specific name

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())

SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

--SELECT cast (@surveyXML as XML)

SET NOCOUNT OFF

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