DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10275
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[ClientName] nvarchar(max),[JobStartDate] int,[JobEndDate] int,[totalNumOfFiles] bigint
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SELECT TOP 3 c.name as ClientName, servStartDate as JobStartDate, servEndDate as JobEndDate, totalNumOfFiles as totalNumOfFiles FROM      
       JMBkpStats (nolock) J
       JOIN App_Application A  (nolock)on J.appId = A.id
       JOIN APP_Client C (nolock) ON c.id = A.clientId       
	   WHERE J.opType = 4 /*Backup*/ AND J.status IN (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 14/*JMSUCCESSWITHWARNINGS*/) 
       AND A.appTypeId = 137 
       AND (A.subclientStatus & 0x2 != 0x2 /*UNINSTALLED*/ AND A.subclientStatus & 0x10 != 0x10 /*DUMMY*/ AND A.subclientStatus & 0x20 != 0x20 /*HIDDEN*/ )
	   AND A.id IN (SELECT componentNameId FROM App_subclientProp A(nolock) 
						WHERE A.attrName = ''Exchange OnePass Subclient Type'' AND A.attrType = 10 AND A.attrVal = ''3'' AND A.modified=0)
       ORDER BY J.servEndDate DESC ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [ClientName] AS '@ClientName',[JobStartDate] AS '@JobStartDate',[JobEndDate] AS '@JobEndDate',[totalNumOfFiles] AS '@totalNumOfFiles' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10275'), 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')) 
