SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--Master

CREATE TABLE [Master]

(

SPID int NOT NULL

IDENTITY(1, 1),

ProjectID INT,

DesignMix VARCHAR(100),

ProjectType VARCHAR(100),

[Date] DATE,

ContentType VARCHAR(100)

)

INSERT INTO [Master]

VALUES (123, 'AB-12', 'New Proj', '1/1/2015', 'CT_1'),

(145, 'AR-13', 'New Proj', '2/1/2015', 'CT_2'),

(423, 'AB-13', 'New Proj', '1/1/2015', 'CT_3')

-- Details

CREATE TABLE [Details]

(

SPID int,

ProjectID INT,

[Length] FLOAT,

TenthReading INT

)

INSERT INTO [Details]

VALUES (1,123, 0.1, 43),

(1,123, 0.1, 45),

(1,123, 0.1, 46),

(1,123, 0.1, 55),

(1,123, 0.1, 59),

(1,123, 0.060, 120),

(2,145, 0.1, 130),

(2,145, 0.1, 45),

(2,145, 0.1, 46),

SPID	ProjectID	DesignMix	ProjectType	Date	ContentType
1	123	AB-12	New Proj	01/01/2015 00:00:00	CT_1
2	145	AR-13	New Proj	01/02/2015 00:00:00	CT_2
3	423	AB-13	New Proj	01/01/2015 00:00:00	CT_3

SPID	ProjectID	Length	TenthReading
1	123	0.1	43
1	123	0.1	45
1	123	0.1	46
1	123	0.1	55
1	123	0.1	59
1	123	0.06	120
2	145	0.1	130
2	145	0.1	45
2	145	0.1	46
2	145	0.1	55
2	145	0.1	59
2	145	0.08	140
3	423	0.077	43
3	423	0.1	45
3	423	0.1	46
3	423	0.1	155
3	423	0.1	59
3	423	0.08	99

ProjectID	DesignMix	Material	Perc	ContentType
123	AB-12	Concrete	20	CT_1
123	AB-12	Limestone	60	CT_1
123	AB-15	Concrete	20	CT_1
145	AR-13	Concrete	20	CT_2
145	AR-13	Concrete	70	CT_2
423	AB-13	Limestone	80	CT_3

Select

Distinct

z.ContentType

,z.Material

,sum(CountLength) over (partition by Material,ContentType order by ContentType) CountLength

,sum(SumLength) over (partition by Material,ContentType order by ContentType) SumLength

,sum(AvgR) over (partition by Material,ContentType order by ContentType) AvgR

,sum(MinR) over (partition by Material,ContentType order by ContentType)MinR

,sum(MaxR) over (partition by Material,ContentType order by ContentType)MaxR

,CountRgreater95

,SumLengthgreater95

From

(

Select

x.ContentType

,CountLength

,SumLength

, AvgR

,MinR

,MaxR

,x.ProjectID

,x.DesignMix

,Coalesce(y.Material,x.Material) Material