DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10272
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Clients] int,[Total Application Size (GB)] decimal(21,3)
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; select count(Client) as Clients, sum(ApplicationSizeGB) as "Total Application Size (GB)"
from (
select  app_client.name as Client, app_application.subclientName as Subclient, JMBkpStats.JobID, CAST((CAST(JMBkpStats.totalUncompBytes as float) / CAST(1073741824 as float)) as decimal(16,2)) as ApplicationSizeGB,
ROW_NUMBER() OVER(PARTITION by app_application.subclientName ORDER BY JMBkpStats.JobID DESC) rn
from JMBkpStats
JOIN App_application ON JMBkpStats.appId=app_application.id
JOIN App_client ON App_client.id=app_application.clientId
AND App_application.id in (select componentNameId from APP_ScFilterFile where filename like ''/Epic%'' or filename like ''%CACHEDB%'' )
AND JMBkpStats.totalUncompBytes != 0
AND JMBkpStats.bkpLevel = 1
)a
where rn=1 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Clients] AS '@Clients',[Total Application Size (GB)] AS '@Col_-2112951495' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10272'), 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')) 
