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

Merry xmas everyone. I'm sorry if my actions were not appropriate. I did not get the option to mark the solution as an answer. I believe this is because the post was marked as a comment rather than an answer.

Answer 2

I have asked @Guoxiong to please mark his comment as an answer or edit the first proposed answer