Agregating data within a table in SQL 2014 with threshold

Question

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

Answer 1

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;

Answer 2

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

Answer 3

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  

 

Answer 4

@Guoxiong please mark your comment as a proposed answer.

Answer 5

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