DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10393
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[numberOfArchivingSubclients] int,[version] nvarchar(max)
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; ;WITH CTE AS
(SELECT
                                TRY_CAST(
                                CASE WHEN CHARINDEX('' '', attrVal, 0) > 0 THEN 
                                    SUBSTRING(attrVal, 0, ISNULL(CHARINDEX('' '', attrVal, 0), LEN(attrVal)))
                                ELSE
                                    attrVal
                                END 
                                AS INT) AS release,

                                TRY_CAST(
                                CASE WHEN CHARINDEX(''SP'', attrVal, 0) > 0 AND CHARINDEX(''.'', attrVal) > 0 THEN
                                    REPLACE(SUBSTRING(attrVal, CHARINDEX(''SP'', attrVal, 0) + 2, CHARINDEX(''.'', attrVal) - CHARINDEX(''SP'', attrVal, 0) - 2), ''+'', '''')
                                
                                                                                                                                WHEN CHARINDEX(''SP'', attrVal, 0) > 0 AND CHARINDEX(''.'', attrVal) = 0 THEN
                                    REPLACE(SUBSTRING(attrVal, CHARINDEX(''SP'', attrVal, 0) + 2, LEN(attrVal) - CHARINDEX(''SP'', attrVal, 0) - 1), ''+'', '''')
                                ELSE
                                    0
                                END
                                AS INT) AS SP,

                                TRY_CAST(
                                CASE WHEN CHARINDEX(''.'', attrVal, 0) > 0 THEN
                                    SUBSTRING(attrVal, CHARINDEX(''.'', attrVal, 0) + 1, LEN(attrVal) - CHARINDEX(''.'', attrVal)) 
                                ELSE
                                    0
                                END
                                AS INT) AS HFP

                FROM APP_ClientProp WITH(NOLOCK)
                WHERE
                attrName = N''SP Version Info''
                AND componentNameId = 2
                AND modified = 0
)

SELECT(
SELECT COUNT(DISTINCT APP.id)
FROM APP_Application APP WITH(NOLOCK)
INNER JOIN APP_BackupsetProp BP WITH (NOLOCK)
ON BP.componentNameId = APP.backupset
       AND BP.attrName = ''Is Archiving Enabled''
       AND BP.attrVal = ''1''
       AND BP.modified = 0
       AND APP.refTime > 1607990400
LEFT JOIN APP_SubClientProp ASP WITH(NOLOCK)
ON APP.id = ASP.componentNameId
       AND ASP.attrName = N''Associated Plan''
       AND ASP.cs_attrName = CHECKSUM(N''Associated Plan'')
       AND ASP.attrVal <> ''0''
       AND ASP.modified = 0
FULL JOIN CTE ON CTE.release = 11
WHERE ASP.componentNameId IS NULL
AND ( (CTE.SP = 22 AND CTE.HFP < 60)
    OR (CTE.SP = 23 AND CTE.HFP < 45)
    OR (CTE.SP = 24 AND CTE.HFP < 32)
    OR (CTE.SP = 25 AND CTE.HFP < 17)
    OR (CTE.SP = 26 AND CTE.HFP < 6)
)) AS ''numberOfArchivingSubclients'',
(SELECT attrVal 
FROM APP_ClientProp WITH(NOLOCK)
WHERE componentNameId = 2 AND attrName = N''SP Version Info'' AND modified = 0
) AS ''version'' ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [numberOfArchivingSubclients] AS '@numberOfArchivingSubclients',[version] AS '@version' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10393'), 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'))