Agregating data within a table in SQL 2014 with threshold

MrFlinstone 441 Reputation points


I have a table/data setup similar like the below, I would like to know whether its possible to have an aggregated data set where only data which meets a specific threshold is shown.

I am trying to create a query that will show srn's and the percentage of servers which has the latest version of the software which is version 2.03

From the example below, SRN 10457 has a total of 8 servers, 7 are non-production that is server_class is UAT or Development, those 7 servers are on the latest version so a column showing percentage of non-production server on the latest version will have a value of 100.

SRN 10583 has 10 servers in total, of which 6 are not on the latest version, so in this case the percentage of non-production servers on the latest version will be 22%.

Will this also be possible within a view ?

The SQL instance is on SQL 2014.

create table #test
srn int,
server_class varchar(30),
software_version varchar(10),
server_count int

INSERT INTO #test SELECT 10457,'UAT','2.03',2
INSERT INTO #test SELECT 10457,'Development','2.03',5
INSERT INTO #test SELECT 10457,'Production','2.03',1
INSERT INTO #test SELECT 10583,'Development','1.05',6
INSERT INTO #test SELECT 10583,'Development','2.05',1
INSERT INTO #test SELECT 10583,'UAT','2.05',1
INSERT INTO #test SELECT 10583,'Production','2.05',1
INSERT INTO #test SELECT 10678,'Production','2.03',1
INSERT INTO #test SELECT 10678,'Development','2.03',1
INSERT INTO #test SELECT 10678,'UAT','2.03',1
INSERT INTO #test SELECT 1250,'Development','2',7
INSERT INTO #test SELECT 1250,'Production','2.043',1
INSERT INTO #test SELECT 1250,'Development','1.05',2
INSERT INTO #test SELECT 1250,'Development','2.043',1
INSERT INTO #test SELECT 1250,'Development','2.05',1
INSERT INTO #test SELECT 1250,'UAT','1.05',1
INSERT INTO #test SELECT 1250,'UAT','2.05',1
INSERT INTO #test SELECT 1250,'Production','1.05',1
INSERT INTO #test SELECT 1250,'Production','2.05',1
INSERT INTO #test SELECT 10797,'Development','2.05',1
INSERT INTO #test SELECT 10797,'Production','2.05',1
INSERT INTO #test SELECT 10797,'QA','2.05',1
INSERT INTO #test SELECT 1267,'Development','2.043',1
INSERT INTO #test SELECT 1267,'Development','2',1
INSERT INTO #test SELECT 1267,'Production','2.043',1
INSERT INTO #test SELECT 1269,'Development','2',6
INSERT INTO #test SELECT 1269,'Development','2.043',1
INSERT INTO #test SELECT 1269,'Production','2.043',1
INSERT INTO #test SELECT 11032,'Development','2.05',1
INSERT INTO #test SELECT 11032,'UAT','2.05',2
INSERT INTO #test SELECT 11032,'QA','2.05',1
INSERT INTO #test SELECT 11032,'Development','2.03',1
INSERT INTO #test SELECT 11032,'Development','2.06',1
INSERT INTO #test SELECT 11032,'Production','2.05',2

select * from #test
4,388 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Guoxiong 8,121 Reputation points

    Suppose the calculations of the latest versions includes those of the production servers. Try this:

    ;WITH CTE_LatestVersion_BySRN AS (
        SELECT SRN, MAX(Software_Version) AS Software_Version
        FROM #test
        GROUP BY SRN
    CTE_Count AS (
        SELECT s.SRN, SUM(s.Server_Count) AS Server_Count, SUM(CASE WHEN s.Software_Version = c.Software_Version THEN s.Server_Count ELSE 0 END) AS LatestVersion_Server_Count
        FROM #test AS s
        JOIN CTE_LatestVersion_BySRN AS c ON c.SRN = s.SRN
        WHERE s.Server_Class <> 'Production'
        GROUP BY s.SRN
    SELECT SRN, LatestVersion_Server_Count * 100 / Server_Count AS LatestVersion_Percentage
    FROM CTE_Count;
    1 person found this answer helpful.

  2. Erland Sommarskog 87,186 Reputation points

    I'm not really clear over how you want to handle the server_class column, but hopefully you can use this query and work from it:

    ; WITH CTE AS (
        SELECT srn, SUM(server_count) AS server_count,
               SUM(CASE WHEN try_convert(decimal(10,3), software_version) < 2.03
                        THEN server_count
                        ELSE 0
                   END) AS behind_count
       FROM #test
       GROUP BY srn
    SELECT srn, convert(decimal(5,2), 1E2*behind_count / server_count)

  3. Nasreen Akter 10,671 Reputation points

    Hi @MrFlinstone ,

    try this:

    DECLARE @version VARCHAR(10);     
    SET @version = '1.05';  
    SELECT srn, server_class, (SUM(CASE WHEN software_version = @version THEN server_count ELSE 0 END) *100)/SUM(server_count)  as 'percentage%',  
    SUM(server_count) as total_server, SUM(CASE WHEN software_version = @version THEN server_count ELSE 0 END) as 'serversEqThr'  
    FROM #test   
    GROUP BY srn, server_class  
    0 comments No comments

  4. MrFlinstone 441 Reputation points

    @Guoxiong please mark your comment as a proposed answer.

    0 comments No comments

  5. EchoLiu-MSFT 14,556 Reputation points

    Hi @MrFlinstone ,

    Congratulations on your problem has been solved, I am glad you have received useful help in the community.

    But in forums, generally speaking, the way to accept answers is to click accept directly on the correct answer as the correct reply.

    As ErlandSommarskog said, you should accept @Guoxiong 's answer as an answer, which is also an affirmation of his time and energy to solve your problem.



    0 comments No comments