clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2043042 fiddles created (16619 in the last week).

--Master CREATE TABLE [Master] ( 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] ( ProjectID INT, [Length] FLOAT, TenthReading INT ) INSERT INTO [Details] VALUES (123, 0.1, 43), (123, 0.1, 45), (123, 0.1, 46), (123, 0.1, 55), (123, 0.1, 59), (123, 0.060, 120), (145, 0.1, 130), (145, 0.1, 45), (145, 0.1, 46), (145, 0.1, 55), (145, 0.1, 59), (145, 0.080, 140), (423, 0.077, 43), (423, 0.1, 45), (423, 0.1, 46), (423, 0.1, 155), (423, 0.1, 59), (423, 0.080, 99) --MaterialType CREATE TABLE [MaterialType] ( ProjectID INT, DesignMix VARCHAR(100), Material VARCHAR(100), Perc INT, ContentType VARCHAR(100) ) INSERT INTO [MaterialType] VALUES (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 * FROM [Master] SELECT * FROM [Details] SELECT * FROM [MaterialType] */
27 rows affected
 hidden batch(es)


select ProjectID, DesignMix, case when count(distinct Material) > 1 then 'Mixed' else min(Material) end as 'Material', ContentType from MaterialType group by ProjectID, DesignMix, ContentType order by 1,2,4
ProjectID DesignMix Material ContentType
123 AB-12 Mixed CT_1
123 AB-15 Concrete CT_1
145 AR-13 Concrete CT_2
423 AB-13 Limestone CT_3
 hidden batch(es)


select ProjectID, count(Length) as CountLength, convert(numeric(6,2),sum(Length)) as SumLength, convert(numeric(6,2),avg(TenthReading*1.0)) as AvgR, min(TenthReading) as MinR, max(TenthReading) as MaxR, sum(case when TenthReading > 95 then 1 else 0 end) as CountRgreater95, sum(case when TenthReading > 95 then Length else 0 end) as SumLengthgreater95 from Details group by ProjectID order by ProjectID
ProjectID CountLength SumLength AvgR MinR MaxR CountRgreater95 SumLengthgreater95
123 6 0.56 61.33 43 120 1 0.06
145 6 0.58 79.17 45 140 2 0.18
423 6 0.56 74.50 43 155 2 0.18
 hidden batch(es)


with mat_type as (select ProjectID, DesignMix, case when count(distinct Material) > 1 then 'Mixed' else min(Material) end as 'Material', ContentType from MaterialType group by ProjectID, DesignMix, ContentType), dtls as (select ProjectID, count(Length) as CountLength, convert(numeric(6,3),sum(Length)) as SumLength, convert(numeric(6,2),avg(TenthReading*1.0)) as AvgR, min(TenthReading) as MinR, max(TenthReading) as MaxR, sum(case when TenthReading > 95 then 1 else 0 end) as CountRgreater95, sum(case when TenthReading > 95 then Length else 0 end) as SumLengthgreater95 from Details group by ProjectID) select m.ContentType, mt.Material, d.CountLength, d.SumLength, d.AvgR, d.MinR, d.MaxR, d.CountRgreater95, d.SumLengthgreater95 from Master m left join dtls d on d.ProjectID = m.ProjectID left join mat_type mt on mt.ProjectID = m.ProjectID and mt.DesignMix = m.DesignMix and mt.ContentType = m.ContentType where m.ProjectTYpe = 'New Proj' and year(m.[Date]) = 2015 order by m.ContentType
ContentType Material CountLength SumLength AvgR MinR MaxR CountRgreater95 SumLengthgreater95
CT_1 Mixed 6 0.560 61.33 43 120 1 0.06
CT_2 Concrete 6 0.580 79.17 45 140 2 0.18
CT_3 Limestone 6 0.557 74.50 43 155 2 0.18
 hidden batch(es)