--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 38
DECLARE @surveyXML NVARCHAR(MAX)
DECLARE @SpUpgardeHistory XML
DECLARE @VersionUpgardeHistory XML

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 

-- Query Name: Deployment History
-- Description: CommServ upgrade history

-- Get the install/upgrade time per release

DECLARE @releaseId INTEGER 
SET @releaseId = (SELECT releaseId FROM APP_Client WITH(NOLOCK) WHERE id = 2) 
 
   DECLARE @outputTbl TABLE(releaseId INT, SPMajor INT, SPMinor INT, installDate DATETIME)
   DECLARE @InstallHistoryTemp TABLE ( SpVer INT, SpMinor INT, OpTime DATETIME)
   DECLARE @curVersionInstallDate DATETIME
   DECLARE @MaxDateTime_v10 DATETIME
   DECLARE @MinDateTime_v10 DATETIME
   DECLARE @MaxDateTime_v11 DATETIME
   DECLARE @MinDateTime_v11 DATETIME

        SET @MaxDateTime_v11 = GETDATE()
            SELECT @MinDateTime_v11 = MIN(CreateTimeUTC) FROM LicUsageHistory WITH(NOLOCK) WHERE ReleaseId=16
    
          SELECT @MaxDateTime_v10 =  ISNULL( (select MIN(CreateTimeUTC) FROM LicUsageHistory WITH(NOLOCK) WHERE ReleaseId=16),getdate()) 

            SELECT @MinDateTime_v10 = ISNULL((select DATEADD(s, CAST(attrVal AS INT), '1970-01-01') FROM App_ClientProp WITH(NOLOCK) WHERE attrname = 'Release 10' AND componentnameid = 2),0)
      
      
IF OBJECT_ID('PatchInstallHistory', 'U') IS NOT NULL
BEGIN
	INSERT INTO @InstallHistoryTemp
	SELECT InstallHistory.C.value('@sp', 'int'),
			InstallHistory.C.value('@spMinor', 'int'),
			DATEADD(S, InstallHistory.C.value('@installTime', 'int'), '1970-01-01')
	FROM (SELECT CAST(stringVal AS XML) AS historyXml
			FROM APP_ComponentProp WITH(NOLOCK)
			WHERE propertyTypeId = 3624 AND componentId = 2
	) AS results
	CROSS APPLY results.historyXml.nodes('/PatchInstallHistory/SPVer') AS InstallHistory(C)
	UNION 
	SELECT	HighestSP,SpMinorVersion, MIN(OpTime)
	FROM	PatchInstallHistory WITH(NOLOCK)
	WHERE ClientId = 2
	GROUP BY HighestSP,SpMinorVersion
	
	INSERT INTO @outputTbl
	SELECT distinct 15, SpVer, SpMinor, min(OpTime)
	FROM @InstallHistoryTemp 
	WHERE OpTime < @MaxDateTime_v10  AND OpTime >= @MinDateTime_v10
	GROUP BY SpVer,SpMinor
	ORDER BY SpVer,SpMinor DESC

	INSERT INTO @outputTbl
	SELECT  16, SpVer,SpMinor, min(OpTime)
	FROM @InstallHistoryTemp 
	WHERE OpTime >= @MinDateTime_v11 AND OpTime < @MaxDateTime_v11  
	GROUP BY SpVer,SpMinor
	ORDER BY SpVer,SpMinor DESC
END

SET @VersionUpgardeHistory = (SELECT 
        releaseId AS '@ReleaseId', 
        (SELECT R.release FROM SimAllGalaxyRel R WHERE R.id = releaseId) AS '@Version', 
        MIN(CreateTimeUTC) AS '@InstallTime',
        (SELECT 
				(releaseId - 5) AS '@Version',
                SPMajor AS '@ServicePack',
                SPMinor AS '@MaintenancePack',
                        2 AS '@ClientId', 
                installDate AS '@InstallationDate'                      
             FROM @outputTbl  o where o.releaseId  =l.releaseId 
             FOR XML PATH ( 'ServicePackUpgradeInfo'),type) as 'ServicePackUpgradeHistory'
        FROM LicUsageHistory l
    WHERE AppType = 1000 
    GROUP BY releaseId
    FOR XML PATH ('SimpanaInstallHistory')) 


SET @surveyXML = (
      
      SELECT CAST (@VersionUpgardeHistory AS NVARCHAR(MAX))

      -- Replace <<RESULTS_NAME>> with specific name
)


--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------


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')
)

SET NOCOUNT OFF

--------- END - GENERATED CODE ---------