unused / unbought License Keys

[Steven___MAGIX]

Hi, we'd like to have two reports that do something like this:

1st report:
- compare purchased keys with retrieved (scanned) keys
and show
- keys which are "purchased" but not "scanned".

In the end we'd like to have a report with keys that have been purchased but not yet used.


2nd report:

- compare purchased keys with retrieved (scanned) keys
and show
- keys which are "scanned" but not "purchased".

So we'd see Keys that are found but no one made a purchase. 😉

Can anyone provide reports like this?
Greetings

[Steven___MAGIX]

Thank you very much! Works like a Charm.

Based on Report 1 I also made a "free operating system keys" Report.
If anyone needs it:

Select Top 1000000 tblOsLicenses.OS As LicenseName,
  tblOssubLicensesOrders.Orderdate,
  tblOssubLicensesOrders.Nrlicenses,
  tblOssubLicensesOrders.Priceperlicense,
  tblOssubLicensesOrders.Ordernumber,
  tblOssubLicensesOrders.Comments,
  Convert(nvarchar,tblOssubLicensesOrders.Licensekey) As LicenseKey
From tblOssubLicensesOrders
  Inner Join tblOsLicenses On tblOsLicenses.OSLicenseidID =
    tblOssubLicensesOrders.OSLicenseidID
Where Convert(nvarchar,tblOssubLicensesOrders.Licensekey) Not In (Select
    tblSerialnumber.ProductKey From tblSerialnumber)

Thanks again!

[Hemoco · Accepted solution]

If you create one order per software key and submit your key in the License Key field, the reports below should work.

To use one of the reports below, do the following:
•Open the report builder under Reports/Create New Report.
•Paste the SQL query we provided at the bottom of the page.
•Left-click somewhere in the upper section of the page so the query applies.
•Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.

Report 1

Select Top 1000000 tblLicenses.softwareName As LicenseName,
tblSublicensesOrders.Orderdate,
tblSublicensesOrders.Nrlicenses,
tblSublicensesOrders.Priceperlicense,
tblSublicensesOrders.Ordernumber,
tblSublicensesOrders.Comments,
Convert(nvarchar,tblSublicensesOrders.Licensekey) As LicenseKey
From tblSublicensesOrders
Inner Join tblLicenses On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID
Where Convert(nvarchar,tblSublicensesOrders.Licensekey) Not In (Select
tblSerialnumber.ProductKey From tblSerialnumber)

Report 2

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Where
tblSerialnumber.ProductKey Not In (Select
Convert(nvarchar,tblSublicensesOrders.Licensekey) As LicenseKey
From tblSublicensesOrders) And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSerialnumber.Product