Query to show software license details

Query to show software license details

PGSQL & MSSQL:

SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition Date",LONGTODATE(Max(resources.expirydate)) "Expiry Date", Max(swinstallationkeyinfo.productid) "Software_ID", Max(swinstallationkeyinfo.productkey) "Software_Key",sdorganization.name "Site" FROM softwarelist LEFT JOIN softwareinfo ON softwarelist.softwareid = softwareinfo.softwareid LEFT JOIN swinstallationkeyinfo ON softwareinfo.softwareinfoid = swinstallationkeyinfo.softwareinfoid LEFT JOIN componentdefinitionsoftware ON softwarelist.softwareid = componentdefinitionsoftware.softwareid LEFT JOIN resources ON componentdefinitionsoftware.componentid = resources.componentid LEFT JOIN softwarelicenses ON resources.resourceid = softwarelicenses.resourceid LEFT join RESOURCELOCATION on softwarelicenses.RESOURCEID=RESOURCELOCATION.RESOURCEID LEFT JOIN sitedefinition on RESOURCELOCATION.siteid=sitedefinition.siteid left join sdorganization on sitedefinition.siteid=sdorganization.org_id GROUP BY resources.resourcename,sdorganization.name 
ORDER BY 1



Below query shows "Expires in" Column but works only in MSSQL:


SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", LONGTODATE(Max(resources.acquisitiondate)) "Acquisition Date",LONGTODATE(Max(resources.expirydate)) "Expiry Date", Max(swinstallationkeyinfo.productid) "Software_ID", Max(swinstallationkeyinfo.productkey) "Software_Key",sdorganization.name "Site",((max(resources.expirydate)/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 "Expires in days" FROM softwarelist LEFT JOIN softwareinfo ON softwarelist.softwareid = softwareinfo.softwareid LEFT JOIN swinstallationkeyinfo ON softwareinfo.softwareinfoid = swinstallationkeyinfo.softwareinfoid LEFT JOIN componentdefinitionsoftware ON softwarelist.softwareid = componentdefinitionsoftware.softwareid LEFT JOIN resources ON componentdefinitionsoftware.componentid = resources.componentid LEFT JOIN softwarelicenses ON resources.resourceid = softwarelicenses.resourceid LEFT join RESOURCELOCATION on softwarelicenses.RESOURCEID=RESOURCELOCATION.RESOURCEID LEFT JOIN sitedefinition on RESOURCELOCATION.siteid=sitedefinition.siteid left join sdorganization on sitedefinition.siteid=sdorganization.org_id GROUP BY resources.resourcename,sdorganization.name 
ORDER BY 4