SLM SQL Agreements

7 years ago

Hi! I have a query for licenses which we are using daily: select ( case when i. type IS NOT then i. type when i. type IS and c . AssignedID is not then 'Agreement' when i. type IS and l. InvoiceReference is not or l. vendor IS NOT then 'Invoice' else end ) AS 'Source' , l. Vendor AS 'Vendor' , IS( c . AssignedID, l. InvoiceReference) AS 'Invoice/agreement number' , l. PurchaseDate AS 'Purchase date' , m. Name AS 'Manufacturer' , af . Name 'Product family' , a . name AS 'LicensedProductName' , l. ProductDescription AS 'ProductDescription' , l. LegalLicenseCount AS 'Quantity' , ( case when c . name like '%Open Value%' then 'Open Value' when c . name like '%Open%' then 'Open' when c . name like '%Enterprise Subscription 6%' then 'Enterprise Subscription 6' when c . name like '%Get Genuine%' then 'Get Genuine' When c . name like '%Select 6%' then 'Select 6' when c . name like '%Enterprise 6%' then 'Enterprise 6' when c . name like '%OSL 6%' then 'OSL 6' when c . name like '%Enterprise 6%' then 'Enterprise 6' when c . name like '%Enterprise Enrollment%' then 'Enterprise Enrollment' when c . name like '%Select Enrollment%' then 'Select Enrollment' end ) AS 'License type/Program' , ( case l. metric when '1' then 'Installations' when '2' then 'Custom compare values' when '7' then 'Number of processors' when '8' then 'Number of processor cores' when '9' then 'Users' when '10' then 'Devices' when '11' then 'Concurrent users' when '12' then 'Concurrent devices' when '13' then 'PVU' when '14' then 'CAL (Client Access License)' end ) AS 'Metric' , ( case when l. IsUpgrade = 1 then 'Upgrade' else 'Base' end ) AS 'Base/Upgrade license' , cast( IS( l. IsSubscription, 0 ) as bit ) AS 'Subscription' , l. SubscriptionValidTo AS 'Subscription end-date' , cast( l. SA as bit ) AS 'Active SA' , cast( ( case when l. IsSubscription = 1 and l. SubscriptionValidTo < getdate( ) then '0' else '1' end ) as bit ) AS 'Included in ELP' from [ dbo] . [ tblLicense] l left join [ dbo] . [ tblContract] c ON l. ContractID = c . ContractID join [ dbo] . [ tblApplication] a ON a . ApplicationID = l. ApplicationID and l. cid = 1 join [ dbo] . [ tblApplicationFamily] af ON af . FamilyIndex = a . FamilyIndex join [ dbo] . [ tblManufacturer] m ON m. ManufacturerID = a . ManufacturerID left join [ dbo] . [ tblImport] i on i. ImportID = l. ImportID order by [ LicensedProductName] ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ There is a mix of different sources used in this query (agreements, imports, etc.). Please, try this one and send feedback if you will want some additional data in. BR, Viktors

Expand Post

dirksc
7 years ago
Thanks a lot Viktors. This is the SQL Query I was looking for.
Expand Post
- Viktors
  7 years ago
  Great! BR, Viktors
  Expand Post
Viktors
7 years ago
Hi! I have a query for licenses which we are using daily: select ( case when i. type IS NOT then i. type when i. type IS and c . AssignedID is not then 'Agreement' when i. type IS and l. InvoiceReference is not or l. vendor IS NOT then 'Invoice' else end ) AS 'Source' , l. Vendor AS 'Vendor' , IS( c . AssignedID, l. InvoiceReference) AS 'Invoice/agreement number' , l. PurchaseDate AS 'Purchase date' , m. Name AS 'Manufacturer' , af . Name 'Product family' , a . name AS 'LicensedProductName' , l. ProductDescription AS 'ProductDescription' , l. LegalLicenseCount AS 'Quantity' , ( case when c . name like '%Open Value%' then 'Open Value' when c . name like '%Open%' then 'Open' when c . name like '%Enterprise Subscription 6%' then 'Enterprise Subscription 6' when c . name like '%Get Genuine%' then 'Get Genuine' When c . name like '%Select 6%' then 'Select 6' when c . name like '%Enterprise 6%' then 'Enterprise 6' when c . name like '%OSL 6%' then 'OSL 6' when c . name like '%Enterprise 6%' then 'Enterprise 6' when c . name like '%Enterprise Enrollment%' then 'Enterprise Enrollment' when c . name like '%Select Enrollment%' then 'Select Enrollment' end ) AS 'License type/Program' , ( case l. metric when '1' then 'Installations' when '2' then 'Custom compare values' when '7' then 'Number of processors' when '8' then 'Number of processor cores' when '9' then 'Users' when '10' then 'Devices' when '11' then 'Concurrent users' when '12' then 'Concurrent devices' when '13' then 'PVU' when '14' then 'CAL (Client Access License)' end ) AS 'Metric' , ( case when l. IsUpgrade = 1 then 'Upgrade' else 'Base' end ) AS 'Base/Upgrade license' , cast( IS( l. IsSubscription, 0 ) as bit ) AS 'Subscription' , l. SubscriptionValidTo AS 'Subscription end-date' , cast( l. SA as bit ) AS 'Active SA' , cast( ( case when l. IsSubscription = 1 and l. SubscriptionValidTo < getdate( ) then '0' else '1' end ) as bit ) AS 'Included in ELP' from [ dbo] . [ tblLicense] l left join [ dbo] . [ tblContract] c ON l. ContractID = c . ContractID join [ dbo] . [ tblApplication] a ON a . ApplicationID = l. ApplicationID and l. cid = 1 join [ dbo] . [ tblApplicationFamily] af ON af . FamilyIndex = a . FamilyIndex join [ dbo] . [ tblManufacturer] m ON m. ManufacturerID = a . ManufacturerID left join [ dbo] . [ tblImport] i on i. ImportID = l. ImportID order by [ LicensedProductName] ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ There is a mix of different sources used in this query (agreements, imports, etc.). Please, try this one and send feedback if you will want some additional data in. BR, Viktors
Expand Post