Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to consolidate data from tables


Query to consolidate data from tables

Author
Message
stento
stento
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 96
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.
Russel Loski
Russel Loski
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 8883
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
stento
stento
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 96
I obviously didn't know how to use the rank() function properly, now I do. Thanks a lot :-D
Russel Loski
Russel Loski
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 8883
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
stento
stento
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 96
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search