June 15, 2013 at 9:14 am
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
June 15, 2013 at 3:23 pm
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:
MachineIdAppNameApplicationInfo
1MSOffice2010MSOffice2010/Alpha
1MSProject2010MSProject2010/Beta
1MSVisio2010MSVisio2010/Beta
1MSVisio2010MSVisio2010/Prod
2MSOffice2010MSOffice2010/Alpha
2MSOffice2010MSOffice2010/Beta
2MSVisio2010MSVisio2010/Prod
3MSOffice2010MSOffice2010/Alpha
3MSOffice2010MSOffice2010/Beta
3MSOffice2010MSOffice2010/Prod
3MSVisio2010MSVisio2010/Alpha
3MSVisio2010MSVisio2010/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:
MachineIdAppName
1MSVisio2010
2MSOffice2010
3MSOffice2010
3MSVisio2010
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:
MachineIdAppNameAppVersion
1MSVisio2010Prod
2MSOffice2010Beta
3MSOffice2010Prod
3MSVisio2010Prod
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply