Jump to content


MIGxxx

License Report - query help

Recommended Posts

Hello,

 

i use the following query to get a license report but i´m not quite happy with the result and don´t know how to edit it.

 

declare @ChannelCode varchar(32)
select @ChannelCode = 'All'
declare @ProductName nvarchar(255)

select
ProductName,
FamilyName,
VersionCode,
coll.Name,
MAX(VersionSequence) as VersionSequence,
MAX(ChannelCode) as ChannelCode,
count(distinct ilp.ResourceID) as InventoryCount
from
dbo.vInventoriedLicensedProduct ilp
left join v_FullCollectionMembership fcm on ilp.ResourceID = fcm.ResourceID
left join v_Collection coll on fcm.CollectionID = coll.CollectionID
where
(ilp.ProductName = @ProductName OR @ProductName IS NULL)
and
(@ChannelCode is NULL or @ChannelCode = N'All' OR ilp.ChannelCode = @ChannelCode)
group by ProductName, FamilyName, VersionCode, coll.Name

 

 

The result is the following:

 

16568286kq.jpg

 

16568288eb.jpg

 

The problem with the result is that every collection is listed in the column Name. But i only want to choose some of them. How can i edit the query to get a result with only three collections ?

 

The best result would be the following, but for this i think i have to modify the whole query.

 

Any experts who can help me ?

 

 

16568496no.jpg

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...