Trying to use MAX SQL function for versions with multiple decimal points

Question

UPDATE: Now that I think about it, using MAX would just give me the latest version and the count numbers that were given for that latest version, not all counts and times added up for "version 10. Please let me know if there is a way around this so I can add up all counts and times for version 10.*

 use CM_CSA
 SELECT DISTINCT
    RS.Name0,
    MU.UserName,
    SF.FileName,
    SF.FileVersion as 'FileVersion',
    MUS.UsageCount as 'UsageCount', 
    MUS.UsageTime as 'UsageTime', 
    MUS.LastUsage as 'LastUsage'
 FROM 
    v_MeteredUser MU
    INNER JOIN (
        SELECT ResourceID,MeteredUserID,FileID,SUM(UsageCount) as 'UsageCount',SUM(UsageTime) as 'UsageTime',MAX(LastUsage) as 'LastUsage'
        FROM v_MonthlyUsageSummary 
        GROUP BY ResourceID,MeteredUserID,FileID
    ) MUS ON MU.MeteredUserID = MUS.MeteredUserID
    INNER JOIN (
        SELECT ResourceID,FileName,FileID,MAX(FileVersion) as 'FileVersion'
        FROM v_GS_SoftwareFile
        GROUP BY FileVersion,ResourceID,FileName,FileID
    ) SF ON MUS.FileID = SF.FileID
    INNER JOIN v_R_System rs on rs.ResourceID = MUS.ResourceID
 WHERE
    SF.FileName like '%acrobat%'
    and MU.UserName like '%jeffrey.toy%'
 ORDER BY MU.UserName, SF.FileName, MUS.LastUsage

The results I"m getting are below. Obviously, the MAX function for FileVersion is not working since there are multiple decimal places. How do I fix this so that I can only show Acrobat version 10 only (no decimal points) so that it adds up all 4 columns. Results:

    Name0        UserName   FileName    FileVersion UsageCount  UsageTime   LastUsage
    GLDLBAE016992   jeffrey.toy Acrobat.exe 10.1.5.33   3   498 6/4/2013
    GLDLBAE016992   jeffrey.toy Acrobat.exe 10.1.7.27   13  6458    9/8/2013
    GLDLBAE016992   jeffrey.toy Acrobat.exe 10.1.8.24   39  2301736 2/18/2014
    GLDLBAE016992   jeffrey.toy Acrobat.exe 10.1.10.18  1   15  6/15/2014
    GLDLBAE016992   jeffrey.toy Acrobat.exe 10.1.9.22   34  818966  6/15/2014

Answer 1

Ok changing my answer as it wasn't helping you. At this point since you have a query that gets you the results and you just need to group them, how about putting the whole thing as another select such as below. The one extra bit I had to do was add the piece to get "10" which was in my original answer.

SELECT Name0,
UserName,
FileName,
FileVersion,
SUM(UsageCount)
SUM(UsageTime)
MAX(LastUsage)
    (SELECT DISTINCT
     RS.Name0,
     MU.UserName,
     SF.FileName,
     left(FileVersion,CHARINDEX('.', FileVersion, 1) - 1) as 'FileVersion',
     MUS.UsageCount as 'UsageCount', 
     MUS.UsageTime as 'UsageTime', 
     MUS.LastUsage as 'LastUsage'
  FROM 
    v_MeteredUser MU
    INNER JOIN (
        SELECT ResourceID,MeteredUserID,FileID,SUM(UsageCount) as 'UsageCount',SUM(UsageTime) as     'UsageTime',MAX(LastUsage) as 'LastUsage'
        FROM v_MonthlyUsageSummary 
        GROUP BY ResourceID,MeteredUserID,FileID
    ) MUS ON MU.MeteredUserID = MUS.MeteredUserID
    INNER JOIN (
        SELECT ResourceID,FileName,FileID,MAX(FileVersion) as 'FileVersion'
        FROM v_GS_SoftwareFile
        GROUP BY FileVersion,ResourceID,FileName,FileID
    ) SF ON MUS.FileID = SF.FileID
    INNER JOIN v_R_System rs on rs.ResourceID = MUS.ResourceID) a
WHERE
  FileName like '%acrobat%'
  and UserName like '%jeffrey.toy%'
GROUP BY Name0,
  UserName,
  FileName,
  FileVersion

Answer 2

You are selecting max(fileversion) and then grouping by Fileversion in your derived table (SF). take fileversion out of your grouping and this should eliminate the row multiplication. Borrowing from the other answer, use max(left(FileVersion,CHARINDEX('.', FileVersion, 1) - 1)) and remove filegversion from the grouping. This should give you one row.

Answer 3

Okay, based on this comment:

I want all counts from all versions to be added up and have it show as Version 10 in a single row. Every time a patch is installed, it creates a separate count and I want to combine all of those counts since it is all Acrobat 10.

There are a few things you need to do:

1. Transform FileVersion so that A.B.C.D is treated as simply A;
2. GROUP BY that A value;
3. Aggregate the fields UsageCount, UsageTime, and LastUsage one more time in your outer SELECT statement.

This can be done, with the caveat that it is not generally performant to GROUP BY a calculated value, since the grouping can't be performed using an index.

There are several different options for the first step; unfortunately, in the case of SQL Server, a built-in string split function is not one of them. You could write a user-defined function, or use @JChao's suggestion; there's also an interesting hack suggested in another question that makes use of PARSENAME():

PARSENAME('10.1.5.33', 4)  -- returns '10'

The limitations of PARSEDNAME() for this purpose are many - it can only recognize . as the separator, it counts elements right-to-left (indexed from 1), it only outputs at most 4 elements, and it doesn't give the integer output you want. If you're confident you won't have to deal with separators other than ., or more than three instances of the separator, you could work out a way to always get the left-most element using e.g. COALESCE()... but that's a lot of contortion, so let's KISS for now, and you can use whatever function works for you in the end. Assuming the version format is always A.B.C.D, you can use:

PARSENAME(FileVersion, 4) AS 'FileVersion'

Now, this is a calculated value, so when you go to GROUP BY you can't just use the alias; you have to provide the calculation expression (or throw your entire query into a subquery... let's not go crazy here):

GROUP BY PARSENAME(FileVersion, 4)

I'm not sure what each of these joins is really doing for you here, but based on the result say you say your original query is giving you, try this out and see if it does what you're looking for:

SELECT DISTINCT
    RS.Name0,
    MU.UserName,
    SF.FileName,
    PARSENAME(SF.FileVersion, 4) as 'FileVersion',
    SUM(MUS.UsageCount) as 'UsageCount', 
    SUM(MUS.UsageTime) as 'UsageTime', 
    MAX(MUS.LastUsage) as 'LastUsage'
 FROM 
    v_MeteredUser MU
    INNER JOIN (SELECT ResourceID, MeteredUserID, FileID, SUM(UsageCount) AS 'UsageCount',
                       SUM(UsageTime) AS 'UsageTime', MAX(LastUsage) AS 'LastUsage'
                FROM v_MonthlyUsageSummary 
                GROUP BY ResourceID, MeteredUserID, FileID
                ) MUS ON MU.MeteredUserID = MUS.MeteredUserID
    INNER JOIN (SELECT ResourceID, FileName, FileID, MAX(FileVersion) as 'FileVersion'
                FROM v_GS_SoftwareFile
                GROUP BY FileVersion, ResourceID, FileName, FileID
                ) SF ON MUS.FileID = SF.FileID
    INNER JOIN v_R_System rs ON rs.ResourceID = MUS.ResourceID
 WHERE
    SF.FileName like '%acrobat%'
    and MU.UserName like '%jeffrey.toy%'
 GROUP BY RS.Name0, MU.UserName, SF.FileName, PARSENAME(FileVersion, 4)
 ORDER BY MU.UserName, SF.FileName, MUS.LastUsage

In the future this sort of thing would be much easier if you stored the version data more atomically, i.e. as separate major, minor, patch, build fields; but I understand that versioning formats vary and you may not be able to fit your data into one consistent format, so perhaps that's just not happening.