→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
moarsmor
Engaged Sweeper
Hi,

Any way to create a report that gives me the same result as the software license compliance does ?
Only the main numbers for each license, not the individual version information..
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Try this to get you going:
SELECT
tblLicenses.softwareName AS [Software Name],
tsyslicensetype.LicenseTypeName AS [License Type],
tblLicenses.Priceperlicense AS [Unit Price],
InUseCount.InUseLicenses AS [In Use],
LicenseCount.PurchasedLicenses AS [Purchased],
(InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) AS [Shortfall],
CASE
WHEN (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) > 0
THEN (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) * tblLicenses.Priceperlicense
ELSE NULL
END AS [Cost],
tblLicenses.LicenseOwner AS [Owner],
tblLicenses.LicenseContact AS [Contact]
FROM
tblLicenses
LEFT JOIN tsyslicensetype ON tsyslicensetype.LicenseType=tblLicenses.LicenseType
LEFT JOIN ( SELECT
tblSublicensesOrders.LicenseidID,
Sum(tblSublicensesOrders.NrLicenses) AS PurchasedLicenses
FROM
tblSublicensesOrders
GROUP BY
tblSublicensesOrders.LicenseidID
) AS LicenseCount ON LicenseCount.LicenseidID=tblLicenses.LicenseidID
LEFT JOIN ( SELECT
tblsublicenses.LicenseidID,
Count(*) AS InUseLicenses
FROM
tblsublicenses
INNER JOIN ( SELECT
tblSoftwareUni.softwareName,
tblSoftware.SoftwareVersion
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID=tblSoftware.softID
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID=tblSoftware.AssetID
WHERE
tblAssetCustom.State=1
) AS Software ON Software.softwareName=tblsublicenses.softwareName
AND Software.softwareVersion=tblsublicenses.softwareVersion
GROUP BY
tblsublicenses.LicenseidID
) AS InUseCount ON InUseCount.LicenseidID=tblLicenses.LicenseidID
ORDER BY
tblLicenses.softwareName

View solution in original post

3 REPLIES 3
moarsmor
Engaged Sweeper
Nice. It works great.
RCorbeil
Honored Sweeper II
A small change to the [Cost] column to account for software being recorded but no purchases associated with that software:
  CASE
WHEN (InUseCount.InUseLicenses - (CASE WHEN LicenseCount.PurchasedLicenses IS NULL
THEN 0
ELSE LicenseCount.PurchasedLicenses
END)) > 0
THEN (InUseCount.InUseLicenses - (CASE WHEN LicenseCount.PurchasedLicenses IS NULL
THEN 0
ELSE LicenseCount.PurchasedLicenses
END)) * tblLicenses.Priceperlicense
ELSE NULL
END AS [Cost],
RCorbeil
Honored Sweeper II
Try this to get you going:
SELECT
tblLicenses.softwareName AS [Software Name],
tsyslicensetype.LicenseTypeName AS [License Type],
tblLicenses.Priceperlicense AS [Unit Price],
InUseCount.InUseLicenses AS [In Use],
LicenseCount.PurchasedLicenses AS [Purchased],
(InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) AS [Shortfall],
CASE
WHEN (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) > 0
THEN (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) * tblLicenses.Priceperlicense
ELSE NULL
END AS [Cost],
tblLicenses.LicenseOwner AS [Owner],
tblLicenses.LicenseContact AS [Contact]
FROM
tblLicenses
LEFT JOIN tsyslicensetype ON tsyslicensetype.LicenseType=tblLicenses.LicenseType
LEFT JOIN ( SELECT
tblSublicensesOrders.LicenseidID,
Sum(tblSublicensesOrders.NrLicenses) AS PurchasedLicenses
FROM
tblSublicensesOrders
GROUP BY
tblSublicensesOrders.LicenseidID
) AS LicenseCount ON LicenseCount.LicenseidID=tblLicenses.LicenseidID
LEFT JOIN ( SELECT
tblsublicenses.LicenseidID,
Count(*) AS InUseLicenses
FROM
tblsublicenses
INNER JOIN ( SELECT
tblSoftwareUni.softwareName,
tblSoftware.SoftwareVersion
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID=tblSoftware.softID
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID=tblSoftware.AssetID
WHERE
tblAssetCustom.State=1
) AS Software ON Software.softwareName=tblsublicenses.softwareName
AND Software.softwareVersion=tblsublicenses.softwareVersion
GROUP BY
tblsublicenses.LicenseidID
) AS InUseCount ON InUseCount.LicenseidID=tblLicenses.LicenseidID
ORDER BY
tblLicenses.softwareName