SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to select specific Application version if machine has multiple times same application installed...


Need to select specific Application version if machine has multiple times same application installed

Author
Message
denis.gendera
denis.gendera
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 95
need some help with the following. I'm using dummy data below to explain what I need.
I've got table that contains information about applications installed on workstation. In our environment applications go through 3 phases (Alpha; Beta; Production) if all goes well when user has Alpha installed, it should automatically upgrade to Beta and then to Production so when we run report it should only show 1 version of specif application installed. like I said if all goes well :-)
we have found instances where workstation has 2 versions installed of same application and we want to filter out the latest version
table has 3 columns machineID, AppName, ApplicationInfo
machineID integer value
AppName string (ShortName for the application)
ApplicationInfo string (FullName for the application including version)
Example 1
machineID AppName ApplicatonInfo
1 MSOffice2010 MSOffice2010/Alpha
1 MSProject2010 MSProject2010/Beta
1 MSVisio2010 MSVisio2010/Prod
1 MSVisio2010 MSVisio2010/Beta

In the example above I only want to report MSVisio2010/Prod (I don't care about the Beta in our environment this is 1 installation)
Example 2
machineID AppName Applicaton
1 MSOffice2010 MSOffice2010/Alpha
1 MSOffice2010 MSOffice2010/Beta
1 MSVisio2010 MSVisio2010/Prod

In the example above I only want to report MSOffice2010/Beta (same comments as before, don't care about the Alpha)
To summarize few scenario's
if machine has Alpha And Beta installed need to report Beta
if machine has Beta And Prod installed need to report Prod
if machine has Alpha, Beta and Prod installed need to report Prod
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2426 Visits: 6494
Hi Denis,

First of all, it is always a good idea to post DDL (in other words scripts to put the table structures together) and a set of sample data (as INSERT statements) that illustrate the problem you are struggling with. Makes it just so much easier for us volunteers to get to a point to help you without having to put this together ourselves.

Anyhow, seeing that you are quite a new visitor, I've done this for you like such:



-- Sample table declaration

DECLARE @Versions TABLE
(
MachineId int NOT NULL,
AppName varchar ( 255 ) NOT NULL,
ApplicationInfo varchar ( 255 ) NOT NULL
);




-- Add some sample data
INSERT INTO @Versions
(
MachineId,
AppName,
ApplicationInfo
)
VALUES
( 1, 'MSOffice2010', 'MSOffice2010/Alpha' ),
( 1, 'MSProject2010', 'MSProject2010/Beta' ),
( 1, 'MSVisio2010', 'MSVisio2010/Prod' ),
( 1, 'MSVisio2010', 'MSVisio2010/Beta' ),

( 2, 'MSOffice2010', 'MSOffice2010/Alpha' ),
( 2, 'MSOffice2010', 'MSOffice2010/Beta' ),
( 2, 'MSVisio2010', 'MSVisio2010/Prod' ),

( 3, 'MSOffice2010', 'MSOffice2010/Alpha' ),
( 3, 'MSOffice2010', 'MSOffice2010/Beta' ),
( 3, 'MSOffice2010', 'MSOffice2010/Prod' ),
( 3, 'MSVisio2010', 'MSVisio2010/Alpha' ),
( 3, 'MSVisio2010', 'MSVisio2010/Prod' )





-- Check what we have inserted
SELECT
MachineId,
AppName,
ApplicationInfo
FROM @Versions
ORDER BY
MachineId,
AppName,
ApplicationInfo




This is the result:


MachineId AppName ApplicationInfo
1 MSOffice2010 MSOffice2010/Alpha
1 MSProject2010 MSProject2010/Beta
1 MSVisio2010 MSVisio2010/Beta
1 MSVisio2010 MSVisio2010/Prod
2 MSOffice2010 MSOffice2010/Alpha
2 MSOffice2010 MSOffice2010/Beta
2 MSVisio2010 MSVisio2010/Prod
3 MSOffice2010 MSOffice2010/Alpha
3 MSOffice2010 MSOffice2010/Beta
3 MSOffice2010 MSOffice2010/Prod
3 MSVisio2010 MSVisio2010/Alpha
3 MSVisio2010 MSVisio2010/Prod



Now, you will see that I have just created all three scenarios that you are talking about. Machine 1 has two versions of Visio, Machine 2 has two version of Office, and Machine 3 has 3 versions of Office and 2 versions of Visio.

The next challenge is to figure out which machine has more than one version of which application installed. In order to do that I added a computed column to the table, that extracts the version from the ApplicationInfo column like this:


DECLARE @Versions TABLE
(
MachineId int NOT NULL,
AppName varchar ( 255 ) NOT NULL,
ApplicationInfo varchar ( 255 ) NOT NULL,
AppVersion AS SUBSTRING ( ApplicationInfo, CHARINDEX ( '/', ApplicationInfo ) + 1, LEN ( ApplicationInfo ) )
);



This works of course only if the version is always separated from the application name by a slash. BTW, storing the data like that is not a good idea, because you are violating third normal form, in other words (under the assumption that MachineId and AppName are a key to the table), ApplicationInfo contains part of that key. In your setup, you could have a scenario where AppName = 'MSSQLFeelsGood', but ApplicationInfo = 'OracleFeelsEvenBetter/Prod'. So I'd recommend getting rid of the AppName in ApplicationInfo. The correct primary key would consist of MachineId, AppName and AppVersion, which makes it unique. After all you can't have two Prod versions of MSOffice installed on the same machine, can you?

Anyhow, with the computed column in place we can figure out which machines have more than one version of a given AppName installed as such:


SELECT
MachineId,
AppName
FROM @Versions
GROUP BY
MachineId,
AppName
HAVING
COUNT(*) > 1
ORDER BY
MachineId,
AppName



Which gives the following results:


MachineId AppName
1 MSVisio2010
2 MSOffice2010
3 MSOffice2010
3 MSVisio2010


To get to the result you are after, all we need to do is to join that result set back to the @Versions table, getting the MAX(AppVersion) per MachineId and AppName. This only works, because sorting alphabetically, Alpha comes before Beta (yeah, those ancient Greeks were clever) and Beta comes before Prod (don't ask me who came up with that):


SELECT
V.MachineId,
V.AppName,
MAX(V.AppVersion) AS AppVersion
FROM @Versions V
JOIN
(
SELECT
MachineId,
AppName
FROM @Versions
GROUP BY
MachineId,
AppName
HAVING
COUNT(*) > 1
) G ON G.MachineId = V.MachineId AND G.AppName = V.AppName
GROUP BY
V.MachineId, V.AppName
ORDER BY
V.MachineId, V.AppName




The result looks like this:



MachineId AppName AppVersion
1 MSVisio2010 Prod
2 MSOffice2010 Beta
3 MSOffice2010 Prod
3 MSVisio2010 Prod



Simple, isn't it? All you need to really do is to split the problem into small steps. Build from the bottom up and grow the tree on top of the roots you've layed.

Regards,
Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
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