DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10365
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Vendors] nvarchar(max),[Snap Engines] nvarchar(max),[Total Meditech Clients] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @MeditechClients TABLE(SnapVendorName NVARCHAR(1024), SnapEngineName NVARCHAR(1024), ClientCount INT)
DECLARE @Result TABLE(SnapVendors NVARCHAR(MAX), SnapEngines NVARCHAR(MAX), TotalClients INT)

DECLARE @SnapVendors NVARCHAR(MAX)
DECLARE @SnapEngines NVARCHAR(MAX)
DECLARE @clients INT

INSERT INTO @MeditechClients
SELECT DISTINCT SE.SnapVendorName, SE.SnapEngineName, COUNT(distinct C.id) ''No. of clients''
FROM APP_Client C
INNER JOIN APP_Application A
       ON C.id = A.clientId AND A.appTypeId = 33
inner join APP_BackupsetName B
       ON A.backupset = B.id
INNER JOIN APP_BackupsetProp BP
       ON B.id = BP.componentNameId AND BP.attrName = ''Custom Application Type'' and BP.attrVal = ''1'' AND BP.modified = 0
INNER JOIN APP_SubClientProp SCP
       ON SCP.componentNameId = A.id AND SCP.attrName = ''Snap Engine'' AND SCP.modified = 0
INNER JOIN SMSnapShotEngine SE ON SE.SnapShotEngineId = SCP.attrVal
GROUP BY SE.SnapVendorName, SE.SnapEngineName


SELECT @SnapVendors = COALESCE(@SnapVendors + '', '', '''') + SnapVendorName
FROM @MeditechClients

SELECT @SnapEngines = COALESCE(@SnapEngines + '', '', '''') + SnapEngineName
FROM @MeditechClients

SET @clients = (SELECT SUM(clientCount) FROM @MeditechClients)

IF (@clients > 0)
BEGIN
INSERT INTO @Result
SELECT @SnapVendors AS ''Vendors'', @SnapEngines AS ''Snap Engines'', @Clients ''Total Meditech clients''
END

SELECT SnapVendors AS ''Vendors'', SnapEngines AS ''Snap Engines'', TotalClients AS ''Total Meditech Clients'' FROM @Result ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Vendors] AS '@Vendors',[Snap Engines] AS '@Col_-429433829',[Total Meditech Clients] AS '@Col_1548164247' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10365'), 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'))