need to have data till 3 dots in SQL

I am having data query with me it gives version of windows update

DECLARE @variable VARCHAR

SET @variable = 'Windows client Update'

SELECT DISTINCT (v_GS_WINDOWSUPDATEAGENTVERSIO.Version0) AS version,
    count(v_R_System.Netbios_Name0) AS modelcount,
    'Windows client Update' AS productname,
    (
        CAST((
                CAST(count(*) AS DECIMAL(18, 2)) / CAST((
                        SELECT count(v_R_System.Netbios_Name0)
                        FROM v_R_System
                        ) AS DECIMAL(18, 2))
                ) * 100 AS DECIMAL(18, 2))
        ) AS 'Percent',
    '%' AS per
FROM v_GS_WINDOWSUPDATEAGENTVERSIO
INNER JOIN v_R_System ON v_GS_WINDOWSUPDATEAGENTVERSIO.ResourceID = v_R_System.ResourceID
WHERE v_GS_WINDOWSUPDATEAGENTVERSIO.Version0 <> 'ISNULL'
--ORDER BY v_GS_WINDOWSUPDATEAGENTVERSIO.Version0
GROUP BY v_GS_WINDOWSUPDATEAGENTVERSIO.Version0

output we are getting

    version modelcount  productname Percent per
    10.0.14393.0    2   Windows client Update   0.00    %
    10.0.14393.1670 84  Windows client Update   0.11    %
    10.0.14393.2189 2   Windows client Update   0.00    %
    10.0.14393.2273 3   Windows client Update   0.00    %
    10.0.14393.2395 265 Windows client Update   0.33    %
    10.0.14393.2608 3   Windows client Update   0.00    %
    10.0.14393.351  7   Windows client Update   0.01    %
    10.0.15063.0    480 Windows client Update   0.61    %
    10.0.15063.1155 11  Windows client Update   0.01    %
    10.0.15063.1235 278 Windows client Update   0.35    %
    10.0.15063.1292 29  Windows client Update   0.04    %
    10.0.15063.1446 8455    Windows client Update   10.67   %
    10.0.15063.483  161 Windows client Update   0.20    %
    10.0.15063.966  1   Windows client Update   0.00    %
    10.0.16299.637  1   Windows client Update   0.00    %
    10.0.17134.1    10  Windows client Update   0.01    %
    10.0.17134.320  2   Windows client Update   0.00    %
    10.0.17134.376  2   Windows client Update   0.00    %
    10.0.17134.471  231 Windows client Update   0.29    %
    10.0.17763.107  10  Windows client Update   0.01    %
    10.0.17763.168  10  Windows client Update   0.01    %
    10.0.17763.292  55  Windows client Update   0.07    %
    10.0.18282.1000 1   Windows client Update   0.00    %
    7.4.7600.226    571 Windows client Update   0.72    %
    7.5.7601.17514  27  Windows client Update   0.03    %
    7.6.7600.256    606 Windows client Update   0.76    %
    7.6.7600.320    1   Windows client Update   0.00    %

7.6.7601.19016  10  Windows client Update   0.01    %
7.6.7601.19161  80  Windows client Update   0.10    %
7.6.7601.23735  129 Windows client Update   0.16    %
7.6.7601.23806  6489    Windows client Update   8.19    %
7.6.7601.24085  51081   Windows client Update   64.46   %
7.8.9200.16384  2   Windows client Update   0.00    %
7.8.9200.16547  171 Windows client Update   0.22    %
7.9.9600.16403  15  Windows client Update   0.02    %
7.9.9600.16422  1   Windows client Update   0.00    %
7.9.9600.17092  8   Windows client Update   0.01    %
7.9.9600.17415  11  Windows client Update   0.01    %
7.9.9600.18696  18  Windows client Update   0.02    %
7.9.9600.18838  41  Windows client Update   0.05    %
7.9.9600.18970  200 Windows client Update   0.25    %
7.9.9600.19164  2026    Windows client Update   2.56    %

I need version up to 3 dots like 7.6.7601 or 7.9.9600 and count be addition of following

10.0.14393.0    2   Windows client Update   0.00    %
10.0.14393.1670 84  Windows client Update   0.11    %
10.0.14393.2189 2   Windows client Update   0.00    %
10.0.14393.2273 3   Windows client Update   0.00    %
10.0.14393.2395 265 Windows client Update   0.33    %
10.0.14393.2608 3   Windows client Update   0.00    %
10.0.14393.351  7

84+2+3+265+3+7 in table

Means I will be needing one row for version 10.0.14393 total count

Answer

The following will create a substring of version0 up to but not including the last dot and anything after that dot

SELECT SUBSTRING(v.version0, 1, 
                 LEN(v.version0) - CHARINDEX('.', REVERSE(v.version0))) AS version

I have used an alias for the table name to make it more readable

FROM v_GS_WINDOWSUPDATEAGENTVERSIO AS v

Advertisement

Answer