DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10270
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[scgt200] int,[scgt1k] int,[bsgt200] int,[bsgt1k] int,[scclgt200] int,[scclgt1k] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @sc TABLE 
  ( 
     sccount   INT, 
     backupset INT, 
     clientid  INT 
  ) 
DECLARE @bs TABLE 
  ( 
     bscount  INT, 
     clientid INT 
  ) 
DECLARE @scclient TABLE 
  ( 
     sccount  INT, 
     clientid INT 
  ) 

INSERT INTO @sc 
SELECT Count(id), 
       backupset, 
       clientid 
FROM   app_application WITH(nolock) 
GROUP  BY clientid, 
          backupset 

INSERT INTO @bs 
SELECT Count(backupset), 
       clientid 
FROM   app_application WITH(nolock) 
GROUP  BY clientid 

INSERT INTO @scclient 
SELECT Count(id), 
       clientid 
FROM   app_application WITH(nolock) 
GROUP  BY clientid 

DECLARE @sc200   INT, 
        @sc1k    INT, 
        @bs200   INT, 
        @bs1k    INT, 
        @sccl200 INT, 
        @sccl1k  INT 

SELECT @sc200 = Sum(CASE 
                      WHEN sccount > 200 THEN 1 
                    END), 
       @sc1k = Sum(CASE 
                     WHEN sccount > 1000 THEN 1 
                   END) 
FROM   @sc 

SELECT @bs200 = Sum(CASE 
                      WHEN bscount > 200 THEN 1 
                    END), 
       @bs1k = Sum(CASE 
                     WHEN bscount > 1000 THEN 1 
                   END) 
FROM   @bs 

SELECT @sccl200 = Sum(CASE 
                        WHEN sccount > 200 THEN 1 
                      END), 
       @sccl1k = Sum(CASE 
                       WHEN sccount > 1000 THEN 1 
                     END) 
FROM   @scclient 

SELECT Isnull(@sc200, 0)   scgt200, 
       Isnull(@sc1k, 0)    scgt1k, 
       Isnull(@bs200, 0)   bsgt200, 
       Isnull(@bs1k, 0)    bsgt1k, 
       Isnull(@sccl200, 0) scclgt200, 
       Isnull(@sccl1k, 0)  scclgt1k ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [scgt200] AS '@scgt200',[scgt1k] AS '@scgt1k',[bsgt200] AS '@bsgt200',[bsgt1k] AS '@bsgt1k',[scclgt200] AS '@scclgt200',[scclgt1k] AS '@scclgt1k' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10270'), 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')) 
