DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10390
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Number of clients] int,[backup size in GB] decimal(21,3)
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SAME_COMMCELL_GALAXY_PACKAGES_ABSENT INT = 2


IF OBJECT_ID(''tempdb.dbo.#uncBackupSize'') IS NOT NULL
    DROP TABLE #uncBackupSize

CREATE TABLE #uncBackupSize(subclientId INT PRIMARY KEY,
                                                       clientId INT,
                                                       backupSize BIGINT DEFAULT 0)

--Identify physical clients with UNC content.
INSERT #uncBackupSize(clientId, subclientId)
SELECT DISTINCT subclient.clientId, subclient.id
FROM APP_ScFilterFile content
INNER JOIN APP_Application subclient ON subclient.id = content.componentNameId
INNER JOIN APP_Client client ON client.id = subclient.clientId
WHERE fileName like ''UNC-NT%'' AND content.modified = 0 AND content.type = 1
AND (specialClientFlags & @SAME_COMMCELL_GALAXY_PACKAGES_ABSENT = 0) AND client.id <> 1


--Identify backup size of each subclient.
UPDATE T
       SET T.backupSize = bkp.bkpSize
FROM #uncBackupSize T
INNER JOIN 
(SELECT JMB.appId, ISNULL(SUM(JMB.totalUncompBytes),0) AS bkpSize
       FROM JMBkpStats JMB
       INNER JOIN #uncBackupSize subclient ON subclient.subclientId = JMB.appId
       WHERE JMB.status in (1,3,14) and JMB.datastatus=0
       GROUP BY JMB.appId) bkp ON T.subclientId = bkp.appId

SELECT COUNT(DISTINCT clientId) AS ''Number of clients'', ISNULL(SUM(CAST((backupSize * 1.0)/ (1024 * 1024 * 1024) as decimal(18,6))), 0) AS ''backup size in GB''
FROM #uncBackupSize
WHERE backupSize != 0 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Number of clients] AS '@Col_-1229951530',[backup size in GB] AS '@Col_-1496094219' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10390'), type
)
  FOR XML PATH ('SurveyResults') 
)

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