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

Need to select specific Application version if machine has multiple times same application installed Expand / Collapse
Author
Message
Posted Saturday, June 15, 2013 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 4, 2013 10:31 AM
Points: 25, Visits: 55
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

Post #1463859
Posted Saturday, June 15, 2013 3:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,116, Visits: 6,438
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)
Post #1463903
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse