→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎07-17-2015 02:11 PM
SELECT licenseview.LicenseID,
licenseview.License,
licenseview.SubCount AS ServersInUse,
licenseview.NrServerlicenses AS ServersPurchased,
CASE
WHEN (tblSqlLicenses.LicenseType = 1
OR tblSqlLicenses.LicenseType = 4) THEN (licenseview.SubCount - licenseview.NrServerlicenses)
ELSE '0'
END AS ServersMissing,
CASE
WHEN tblSqlLicenses.LicenseType = 1 THEN '0'
WHEN (tblSqlLicenses.LicenseType = 5
AND datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
WHEN (tblSqlLicenses.LicenseType = 5) THEN '0'
WHEN (tblSqlLicenses.LicenseType = 4
AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms
ELSE tblSqlLicenses.NrOfTerms * licenseview.SubCount
END AS InUse,
CASE
WHEN (tblSqlLicenses.LicenseType = 5) THEN (CASE
WHEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0 THEN '0'
ELSE datediff(m, getdate(), tblSqlLicenses.LicenseExpiration) * licenseview.SubCount
END)
ELSE licenseview.Nrlicenses
END AS Purchased,
CASE
WHEN (tblSqlLicenses.LicenseType = 5) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
WHEN (tblSqlLicenses.LicenseType = 4
AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms - licenseview.Nrlicenses
ELSE (tblSqlLicenses.NrOfTerms * licenseview.SubCount) - licenseview.Nrlicenses
END AS Missing,
tsysSqlLicenseTypes.LicenseTypeName AS TYPE,
tsysSqlLicenseTypes.LicenseType AS TypeID,
tblSqlLicenses.PricePerServer,
tblSqlLicenses.PricePerTerm,
Usedservers.NAME,
tblSqlLicenses.LicenseContact,
tblSqlLicenses.LicenseOwner,
Usedservers.NAME AS SubName,
Usedservers.nrofservers
FROM tblSqlLicenses
INNER JOIN
(SELECT DISTINCT Purchased.LicenseID,
Purchased.NAME AS License,
(CASE WHEN Purchased2.nrlicenses IS NULL THEN '0' ELSE Purchased2.nrlicenses END) AS Nrlicenses,
(CASE WHEN Purchased.nrserverlicenses IS NULL THEN '0' ELSE Purchased.nrserverlicenses END) AS NrServerlicenses,
Purchased.PricePerServer,
Nametotal.nrofservers AS SubCount
FROM tblSqlSubServers
INNER JOIN
(SELECT LicenseID,
count(nrofservers) AS nrofservers
FROM
(SELECT DISTINCT tblSqlSubServers_1.LicenseID,
tblAssets.AssetID AS nrofservers
FROM tblAssetCustom
INNER JOIN tblSqlServers ON tblAssetCustom.AssetID = tblSqlServers.AssetID
INNER JOIN tblSqlSubServers AS tblSqlSubServers_1 ON tblSqlServers.displayVersion + ' ' + tblSqlServers.skuName LIKE tblSqlSubServers_1.NAME
INNER JOIN tblAssets ON tblAssetCustom.AssetID = tblAssets.AssetID
AND tblSqlServers.AssetID = tblAssets.AssetID
AND tblAssets.AssetName = tblSqlSubServers_1.AssetName
WHERE (tblAssetCustom.STATE = 1)) T
GROUP BY LicenseID) AS Nametotal ON Nametotal.LicenseID = tblSqlSubServers.LicenseID
RIGHT JOIN
(SELECT tblSqlLicenses_1.NAME,
tblSqlLicenses_1.PricePerServer,
tblSqlLicenses_1.LicenseID,
SUM(nrserverlicenses.Nrlicenses) AS nrserverlicenses
FROM tblSqlLicenses AS tblSqlLicenses_1
LEFT JOIN tblSqlLicensesServerOrders AS nrserverlicenses ON nrserverlicenses.LicenseID = tblSqlLicenses_1.LicenseID
GROUP BY tblSqlLicenses_1.NAME,
tblSqlLicenses_1.PricePerServer,
tblSqlLicenses_1.LicenseID) AS Purchased ON tblSqlSubServers.LicenseID = Purchased.LicenseID
RIGHT JOIN
(SELECT tblSqlLicenses_3.NAME,
tblSqlLicenses_3.LicenseID,
SUM(nrlicenses_3.Nrlicenses) AS nrlicenses,
tblSqlLicenses_3.PricePerTerm
FROM tblSqlLicenses AS tblSqlLicenses_3
LEFT JOIN tblSqlLicensesOrders AS nrlicenses_3 ON nrlicenses_3.LicenseID = tblSqlLicenses_3.LicenseID
GROUP BY tblSqlLicenses_3.LicenseID,
tblSqlLicenses_3.NAME,
tblSqlLicenses_3.LicenseID,
tblSqlLicenses_3.PricePerTerm) AS Purchased2 ON tblSqlSubServers.LicenseID = Purchased2.LicenseID
GROUP BY Purchased.LicenseID,
Purchased.NAME,
Purchased2.nrlicenses,
Purchased.nrserverlicenses,
Purchased.PricePerServer,
Nametotal.nrofservers) AS licenseview ON tblSqlLicenses.LicenseID = licenseview.LicenseID
INNER JOIN tsysSqlLicenseTypes ON tblSqlLicenses.LicenseType = tsysSqlLicenseTypes.LicenseType
INNER JOIN
(SELECT NAME,
LicenseID,
count(assetid) AS nrofservers
FROM
(SELECT DISTINCT RTRIM(LTRIM(COALESCE(tblSqlServers_1.displayVersion, N'') + ' ' + COALESCE(tblSqlServers_1.skuName, N''))) AS NAME,
tblSqlSubServers_2.LicenseID,
tblAssetCustom_1.AssetID
FROM tblAssetCustom AS tblAssetCustom_1
INNER JOIN tblSqlServers AS tblSqlServers_1 ON tblAssetCustom_1.AssetID = tblSqlServers_1.AssetID
INNER JOIN tblSqlSubServers AS tblSqlSubServers_2 ON tblSqlServers_1.displayVersion + ' ' + tblSqlServers_1.skuName LIKE tblSqlSubServers_2.NAME
INNER JOIN tblAssets AS tblAssets_1 ON tblAssetCustom_1.AssetID = tblAssets_1.AssetID
AND tblSqlServers_1.AssetID = tblAssets_1.AssetID
AND tblAssets_1.AssetName = tblSqlSubServers_2.AssetName
WHERE (tblAssetCustom_1.STATE = 1)) T
GROUP BY Name,
LicenseID) AS Usedservers ON Usedservers.LicenseID = tblSqlLicenses.LicenseID
ORDER BY TypeID,
licenseview.License,
tblSqlLicenses.Name
SELECT licenseview.LicenseID,
licenseview.License,
licenseview.SubCount AS ServersInUse,
licenseview.NrServerlicenses AS ServersPurchased,
CASE
WHEN (tblSqlLicenses.LicenseType = 1
OR tblSqlLicenses.LicenseType = 4) THEN (licenseview.SubCount - licenseview.NrServerlicenses)
ELSE '0'
END AS ServersMissing,
CASE
WHEN tblSqlLicenses.LicenseType = 1 THEN '0'
WHEN (tblSqlLicenses.LicenseType = 5
AND datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
WHEN (tblSqlLicenses.LicenseType = 5) THEN '0'
WHEN (tblSqlLicenses.LicenseType = 4
AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms
ELSE tblSqlLicenses.NrOfTerms * licenseview.SubCount
END AS InUse,
CASE
WHEN (tblSqlLicenses.LicenseType = 5) THEN (CASE
WHEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0 THEN '0'
ELSE datediff(m, getdate(), tblSqlLicenses.LicenseExpiration) * licenseview.SubCount
END)
ELSE licenseview.Nrlicenses
END AS Purchased,
CASE
WHEN (tblSqlLicenses.LicenseType = 5) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
WHEN (tblSqlLicenses.LicenseType = 4
AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms - licenseview.Nrlicenses
ELSE (tblSqlLicenses.NrOfTerms * licenseview.SubCount) - licenseview.Nrlicenses
END AS Missing,
tsysSqlLicenseTypes.LicenseTypeName AS TYPE,
tsysSqlLicenseTypes.LicenseType AS TypeID,
tblSqlLicenses.PricePerServer,
tblSqlLicenses.PricePerTerm,
Usedservers.Name,
tblSqlLicenses.LicenseContact,
tblSqlLicenses.LicenseOwner,
Usedservers.Name AS SubName,
Usedservers.nrofservers
FROM tblSqlLicenses
INNER JOIN
(SELECT DISTINCT Purchased.LicenseID,
Purchased.Name AS License,
(CASE WHEN Purchased2.nrlicenses IS NULL THEN '0' ELSE Purchased2.nrlicenses END) AS Nrlicenses,
(CASE WHEN Purchased.nrserverlicenses IS NULL THEN '0' ELSE Purchased.nrserverlicenses END) AS NrServerlicenses,
Purchased.PricePerServer,
Nametotal.nrofservers AS SubCount
FROM tblSqlSubServers
INNER JOIN
(SELECT tblSqlSubServers_1.LicenseID,
COUNT(tblAssets.AssetID) AS nrofservers
FROM tblAssetCustom
INNER JOIN tblSqlServers ON tblAssetCustom.AssetID = tblSqlServers.AssetID
INNER JOIN tblSqlSubServers AS tblSqlSubServers_1 ON tblSqlServers.displayVersion + ' ' + tblSqlServers.skuName LIKE tblSqlSubServers_1.Name
INNER JOIN tblAssets ON tblAssetCustom.AssetID = tblAssets.AssetID
AND tblSqlServers.AssetID = tblAssets.AssetID
AND tblAssets.AssetName LIKE tblSqlSubServers_1.AssetName
WHERE (tblAssetCustom.State = 1)
GROUP BY tblSqlSubServers_1.LicenseID) AS Nametotal ON Nametotal.LicenseID = tblSqlSubServers.LicenseID
RIGHT OUTER JOIN
(SELECT tblSqlLicenses_1.Name,
tblSqlLicenses_1.PricePerServer,
tblSqlLicenses_1.LicenseID,
SUM(nrserverlicenses.Nrlicenses) AS nrserverlicenses
FROM tblSqlLicenses AS tblSqlLicenses_1
LEFT OUTER JOIN tblSqlLicensesServerOrders AS nrserverlicenses ON nrserverlicenses.LicenseID = tblSqlLicenses_1.LicenseID
GROUP BY tblSqlLicenses_1.Name,
tblSqlLicenses_1.PricePerServer,
tblSqlLicenses_1.LicenseID) AS Purchased ON tblSqlSubServers.LicenseID = Purchased.LicenseID
RIGHT OUTER JOIN
(SELECT tblSqlLicenses_3.Name,
tblSqlLicenses_3.LicenseID,
SUM(nrlicenses_3.Nrlicenses) AS nrlicenses,
tblSqlLicenses_3.PricePerTerm
FROM tblSqlLicenses AS tblSqlLicenses_3
LEFT OUTER JOIN tblSqlLicensesOrders AS nrlicenses_3 ON nrlicenses_3.LicenseID = tblSqlLicenses_3.LicenseID
GROUP BY tblSqlLicenses_3.LicenseID,
tblSqlLicenses_3.Name,
tblSqlLicenses_3.LicenseID,
tblSqlLicenses_3.PricePerTerm) AS Purchased2 ON tblSqlSubServers.LicenseID = Purchased2.LicenseID
GROUP BY Purchased.LicenseID,
Purchased.Name,
Purchased2.nrlicenses,
Purchased.nrserverlicenses,
Purchased.PricePerServer,
Nametotal.nrofservers) AS licenseview ON tblSqlLicenses.LicenseID = licenseview.LicenseID
INNER JOIN tsysSqlLicenseTypes ON tblSqlLicenses.LicenseType = tsysSqlLicenseTypes.LicenseType
INNER JOIN
(SELECT RTRIM(LTRIM(COALESCE (tblSqlServers_1.displayVersion, N'') + ' ' + COALESCE (tblSqlServers_1.skuName, N''))) AS Name,
tblSqlSubServers_2.LicenseID,
COUNT(tblAssets_1.AssetID) AS nrofservers
FROM tblAssetCustom AS tblAssetCustom_1
INNER JOIN tblSqlServers AS tblSqlServers_1 ON tblAssetCustom_1.AssetID = tblSqlServers_1.AssetID
INNER JOIN tblSqlSubServers AS tblSqlSubServers_2 ON tblSqlServers_1.displayVersion + ' ' + tblSqlServers_1.skuName LIKE tblSqlSubServers_2.Name
INNER JOIN tblAssets AS tblAssets_1 ON tblAssetCustom_1.AssetID = tblAssets_1.AssetID
AND tblSqlServers_1.AssetID = tblAssets_1.AssetID
AND tblAssets_1.AssetName LIKE tblSqlSubServers_2.AssetName
WHERE (tblAssetCustom_1.State = 1)
GROUP BY RTRIM(LTRIM(COALESCE (tblSqlServers_1.displayVersion, N'') + ' ' + COALESCE (tblSqlServers_1.skuName, N''))),
tblSqlSubServers_2.LicenseID) AS Usedservers ON Usedservers.LicenseID = tblSqlLicenses.LicenseID
ORDER BY TypeID,
licenseview.License,
tblSqlLicenses.Name
Solved! Go to Solution.
‎07-24-2015 11:57 AM
‎08-11-2015 01:32 PM
‎08-11-2015 11:09 AM
‎08-11-2015 09:39 AM
‎07-24-2015 11:57 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now