February 12, 2014 at 7:47 am
I am struggling with a somewhat tricky query. I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------
I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),
.
.
(15, 'Microsoft Visual Studio Professional 2005');
WITH
cteUsersAndSoftware(UserName, Software)
AS
(
SELECT DISTINCT s.User_Domain0 + '\' + s.User_Name0 AS 'UserName',
(CASE
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Ultimate% %2013%' OR ... THEN 'Microsoft Visual Studio Ultimate 2013'
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Ultimate% %2012%' OR ... THEN 'Microsoft Visual Studio Ultimate 2012'
.
.
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Professional% %2005%' OR ... THEN 'Microsoft Visual Studio Professional 2005'
END) AS 'Software'
FROM dbo.v_Add_Remove_Programs p
INNER JOIN dbo.v_R_System s ON p.ResourceID = s.ResourceID
)
SELECT DISTINCT cte.UserName, rnk.vsVersion
FROM cteUsersAndSoftware cte
INNER JOIN (SELECT vsVersion AS 'vsVersion', MIN(rankID) AS 'rankID' FROM #swRank GROUP BY vsVersion) rnk ON cte1.Software = rnk.vsVersion
Any help here would be most appreciated.
February 12, 2014 at 8:23 am
How about:
WITH cteUsersAndSoftware(UserName, Software)
AS
(
SELECT DISTINCT s.User_Domain0 + '\' + s.User_Name0 AS 'UserName',
(CASE
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Ultimate% %2013%' OR ... THEN 'Microsoft Visual Studio Ultimate 2013'
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Ultimate% %2012%' OR ... THEN 'Microsoft Visual Studio Ultimate 2012'
.
.
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Professional% %2005%' OR ... THEN 'Microsoft Visual Studio Professional 2005'
END) AS 'Software'
FROM dbo.v_Add_Remove_Programs p
INNER JOIN dbo.v_R_System s ON p.ResourceID = s.ResourceID
) , cteRank as (
SELECT cte.UserName
, rank() over (partition by cte.UserName order by rnk.vsVersion) as ProductRank
, rnk.vsVersion
FROM cteUsersAndSoftware cte
INNER JOIN #swRank
rnk ON cte1.Software = rnk.vsVersion
)
select * from cte
where ProductRank = 1
Russel Loski, MCSE Business Intelligence, Data Platform
February 12, 2014 at 9:05 am
I obviously didn't know how to use the rank() function properly, now I do. Thanks a lot 😀
February 12, 2014 at 9:09 am
One correction (I'm getting used to user interface). You need to order by RankID:
WITH cteUsersAndSoftware(UserName, Software)
AS
(
SELECT DISTINCT s.User_Domain0 + '\' + s.User_Name0 AS 'UserName',
(CASE
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Ultimate% %2013%' OR ... THEN 'Microsoft Visual Studio Ultimate 2013'
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Ultimate% %2012%' OR ... THEN 'Microsoft Visual Studio Ultimate 2012'
.
.
WHEN p.DisplayName0 LIKE 'Microsoft Visual Studio% %Professional% %2005%' OR ... THEN 'Microsoft Visual Studio Professional 2005'
END) AS 'Software'
FROM dbo.v_Add_Remove_Programs p
INNER JOIN dbo.v_R_System s ON p.ResourceID = s.ResourceID
) , cteRank as (
SELECT cte.UserName
, rank() over (partition by cte.UserName order by rnk.RankID) as ProductRank
, rnk.vsVersion
FROM cteUsersAndSoftware cte
INNER JOIN #swRank
rnk ON cte1.Software = rnk.vsVersion
)
select * from cte
where ProductRank = 1
Russel Loski, MCSE Business Intelligence, Data Platform
February 12, 2014 at 9:25 am
This one works perfectly:
.
.
INNER JOIN dbo.v_R_System s ON p.ResourceID = s.ResourceID
) , cteRank AS (
SELECT cte.UserName
, RANK() OVER (PARTITION BY cte.UserName ORDER BY rnk.RankID) AS ProductRank
, rnk.vsVersion
FROM cteUsersAndSoftware cte
INNER JOIN #swRank rnk ON cte.Software = rnk.vsVersion)
SELECT UserName, vsVersion FROM cteRank
WHERE ProductRank = 1
Again, thanks a lot 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply