I'm looking for an SQL query that connect the Licenses to the correct agreements.
I want all data from both tables in this query.
Hope that someone can help me with this.
Thanks in advance.
I have a query for licenses which we are using daily:
select (case when i.type IS NOT NULL then i.type when i.type IS NULL and c.AssignedID is not null then 'Agreement' when i.type IS NULL and l.InvoiceReference is not null or l.vendor IS NOT NULL then 'Invoice' else null end) AS 'Source' ,l.Vendor AS 'Vendor' ,ISNULL(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(ISNULL(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.
Thanks a lot Viktors.
This is the SQL Query I was looking for.
Retrieving data ...