Agregating data within a table in SQL 2014 with threshold

MrFlinstone 481 Reputation points
2020-12-24T15:55:25.693+00:00

Hi,

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2020-12-24T16:44:39.86+00:00

    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 101.4K Reputation points MVP
    2020-12-24T16:26:52.99+00:00

    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)
FROM  CTE
  3. Nasreen Akter 10,751 Reputation points
    2020-12-24T17:10:06.9+00:00

    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
  4. MrFlinstone 481 Reputation points
    2020-12-24T17:14:26.007+00:00

    @Guoxiong please mark your comment as a proposed answer.

    0 comments
  5. EchoLiu-MSFT 14,571 Reputation points
    2020-12-25T07:43:22.89+00:00

    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.

    Thanks.

    Regards
    Echo

    0 comments