clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
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. 497153 fiddles created (9381 in the last week).

--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), (2,145, 0.1, 55), (2,145, 0.1, 59), (2,145, 0.080, 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.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]
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
 hidden batch(es)


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 ,CountRgreater95 ,SumLengthgreater95 From (Select Distinct c.ContentType ,CountLength ,SumLength , AvgR ,MinR ,MaxR ,c.ProjectID ,c.DesignMix ,c.Material ,CountRgreater95 ,SumLengthgreater95 from ( select distinct a.* ,b.Material,max(b.Perc) over (partition by b.Material,b.DesignMix order by a.ProjectID) Perc from (SELECT a.ProjectID,a.DesignMix,a.ContentType, COUNT(b.Length) AS CountLength, SUM(b.Length) AS SumLength, CONVERT(int, ROUND(AVG(CONVERT(decimal(6, 2), b.TenthReading)), 0)) AS AvgR, MIN(b.TenthReading) AS MinR, MAX(b.TenthReading) AS MaxR, c.CountRgreater95, c.SumLengthgreater95 FROM dbo.Master AS a INNER JOIN dbo.Details AS b ON a.SPID = b.SPID INNER JOIN (SELECT x0.ContentType, COUNT(x.Length) AS CountRgreater95, SUM(x.Length) AS SumLengthgreater95 FROM dbo.Master AS x0 INNER JOIN dbo.Details AS x ON x0.SPID = x.SPID WHERE (x.TenthReading >= 95) AND (x0.ProjectType = 'New Proj') AND (YEAR(x0.Date) = 2015) GROUP BY x0.ContentType) AS c ON a.ContentType = c.ContentType WHERE (a.ProjectType = 'New Proj') AND (YEAR(a.Date) = 2015) GROUP BY a.ContentType, YEAR(a.Date), c.CountRgreater95, c.SumLengthgreater95,a.ProjectID,a.DesignMix )a inner join MaterialType b on a.ProjectID=b.ProjectID and a.DesignMix=b.DesignMix ) c group by c.ContentType,ProjectID,DesignMix,Material,CountLength,SumLength,CountRgreater95,SumLengthgreater95,AvgR,MinR,MaxR )x Left Join ( select d.ContentType ,d.ProjectID ,d.DesignMix ,'Mixed' as Material ,count(ProjectID) cnt from ( Select Distinct c.ContentType ,CountLength ,SumLength , AvgR ,MinR ,MaxR ,c.ProjectID ,c.DesignMix ,c.Material ,CountRgreater95 ,SumLengthgreater95 from ( select distinct a.* ,b.Material,max(b.Perc) over (partition by b.Material,b.DesignMix order by a.ProjectID) Perc from (SELECT a.ProjectID,a.DesignMix,a.ContentType, COUNT(b.Length) AS CountLength, SUM(b.Length) AS SumLength, CONVERT(int, ROUND(AVG(CONVERT(decimal(6, 2), b.TenthReading)), 0)) AS AvgR, MIN(b.TenthReading) AS MinR, MAX(b.TenthReading) AS MaxR, c.CountRgreater95, c.SumLengthgreater95 FROM dbo.Master AS a INNER JOIN dbo.Details AS b ON a.SPID = b.SPID INNER JOIN (SELECT x0.ContentType,x1.Material, COUNT(x.Length) AS CountRgreater95, SUM(x.Length) AS SumLengthgreater95 FROM dbo.Master AS x0 INNER JOIN dbo.Details AS x ON x0.SPID = x.SPID left Join MaterialType x1 on x0.ProjectID=x1.ProjectID and x0.DesignMix=x1.DesignMix WHERE (x.TenthReading >= 95) AND (x0.ProjectType = 'New Proj') AND (YEAR(x0.Date) = 2015) GROUP BY x0.ContentType,x1.Material) AS c ON a.ContentType = c.ContentType WHERE (a.ProjectType = 'New Proj') AND (YEAR(a.Date) = 2015) GROUP BY a.ContentType, YEAR(a.Date), c.CountRgreater95, c.SumLengthgreater95,a.ProjectID,a.DesignMix )a inner join MaterialType b on a.ProjectID=b.ProjectID and a.DesignMix=b.DesignMix ) c group by c.ContentType,ProjectID,DesignMix,Material,CountLength,SumLength,CountRgreater95,SumLengthgreater95,AvgR,MinR,MaxR )d group by d.ContentType,ProjectID,DesignMix Having count(ProjectID)>1 )y on x.ContentType=y.ContentType and x.ProjectID=y.ProjectID and x.DesignMix=y.DesignMix )z
ContentType Material CountLength SumLength AvgR MinR MaxR CountRgreater95 SumLengthgreater95
CT_1 Mixed 12 1.12 122 86 240 1 0.06
CT_2 Concrete 6 0.58 79 45 140 2 0.18
CT_3 Limestone 6 0.557 75 43 155 2 0.18
 hidden batch(es)