I found the following on this site somewhere. (Thank to the author)
Select Top 1000000 tblLicenses.softwareName As [Software Name],
InUseCount.InUseLicenses As [In Use],
LicenseCount.PurchasedLicenses As Purchased,
(InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) As Shortfall
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 [Software Name]
The thing is, if it doesn't find anything for In Use, it leaves the field blank.
What would I need to modify to get it to show a 0 instead of blank.
