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


IF @releaseId >= 15 
BEGIN 
 insert into @temptbl 
EXEC('SET NOCOUNT ON; SELECT 
         (SELECT COUNT(*) FROM APP_Client WHERE status & 4096 = 4096) AS ''DLOClients''
        ,(SELECT COUNT(a.userid) FROM
(SELECT DISTINCT userId ''userid'' FROM UMUserANDGroupEntityAssociatiON WHERE entityType = 2009 AND objectId IN (SELECT id FROM APP_Client WHERE id in (SELECT DISTINCT clientId FROM APP_ApplicatiON WHERE id IN (SELECT appid FROM archFile WHERE flags & 64 = 64)) AND status & 4096 = 4096)  AND userid <> 0
uniON 
SELECT DISTINCT UG.userId ''userid'' FROM UMUserANDGroupEntityAssociatiON UM INNER JOIN UMUserGroup UG ON UM.groupId = UG.groupId WHERE entityType = 2009 AND objectId IN (SELECT id FROM APP_Client WHERE id in (SELECT DISTINCT clientId FROM APP_ApplicatiON WHERE id IN (SELECT appid FROM archFile WHERE flags & 64 = 64)) AND status & 4096 = 4096) AND UM.userid = 0 AND UM.providerId = 0
uniON
SELECT DISTINCT UG.userId ''userid'' FROM UMUserANDGroupEntityAssociatiON UM INNER JOIN UMDSUserGroup UG ON UM.groupId = UG.groupId WHERE entityType = 2009 AND objectId IN (SELECT id FROM APP_Client WHERE id in (SELECT DISTINCT clientId FROM APP_ApplicatiON WHERE id IN (SELECT appid FROM archFile WHERE flags & 64 = 64)) AND status & 4096 = 4096) AND UM.userid = 0 AND UM.providerId <> 0
uniON
SELECT DISTINCT UG.userId ''userid'' FROM UMUserANDGroupEntityAssociatiON UM INNER JOIN UMDSGroupMaps UGM ON UM.groupId = UGM.umgroupId INNER JOIN UMDSUserGroup UG ON UGM.umDSgroupId = UG.groupId  WHERE entityType = 2009 AND objectId IN (SELECT id FROM APP_Client WHERE id in (SELECT DISTINCT clientId FROM APP_ApplicatiON WHERE id IN (SELECT appid FROM archFile WHERE flags & 64 = 64)) AND status & 4096 = 4096) AND UM.userid = 0 AND UM.providerId = 0
) a) AS ''DLOCIUsers''
 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
Select [DLOClients] AS '@DLOClients',[DLOCIUsers] AS '@DLOCIUsers' from @temptbl
 FOR XML PATH ('cf_MetricsQuery10011'), 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') 
)