Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to consolidate data from tables Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:01 PM
Points: 3, Visits: 55
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.
Post #1540737
Posted Wednesday, February 12, 2014 8:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, June 7, 2014 4:22 AM
Points: 565, Visits: 8,703
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, MCITP
Post #1540777
Posted Wednesday, February 12, 2014 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:01 PM
Points: 3, Visits: 55
I obviously didn't know how to use the rank() function properly, now I do. Thanks a lot
Post #1540824
Posted Wednesday, February 12, 2014 9:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, June 7, 2014 4:22 AM
Points: 565, Visits: 8,703
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, MCITP
Post #1540828
Posted Wednesday, February 12, 2014 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:01 PM
Points: 3, Visits: 55
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
Post #1540855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse