SQL Server Join – Joining Multiple Tables with Complex Requirements

join;sql server

I have 3 Tables like this:

Master: PK: SPID

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

Details: PK: There is an Identity column ID

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.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: PK is an Identity column ID

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

The query specifications are these:
1. Join master and details table based on SPID
2. Join master and Material table based on ProjectID and DesignMix
3. If a specific projectID and DesignMix have multiple rows in MaterialTable eg. Concrete and Limestone for ID=123, then it should be
merged together with the name 'Mixed'
4. All data from Master table and relevant data from Details and Materials table i.e. Left joins preferred especially between Master and Materials
5. Date should be year 2015 and Project Type should be 'New Proj'

The resulting query should give me something like this: 

ContentType|Material |CountLength|SumLength|AvgR |MinR|MaxR|CountRgreater95|SumLengthgreater95  
CT_1       |Mixed    |6          |0.56     |61.33|43  |120 |1              |0.06  
CT_2       |Concrete |6          |0.58     |79.16|45  |140 |2              |0.18  
CT_3       |Limestone|6          |0.557    |74.5 |43  |155 |2              |0.18

Here's a DBFiddle link providing test table definitions

This is the query I wrote so far but its not giving me the correct results:

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

Check the DBFiddle link to see my results

Best Answer

Going to make some assumptions since there are a handful of discrepancies in the original question:

  • ignore SPID column; missing from dbfiddle; missing from sample query
  • join Master and Details on ProjectID
  • join Master and MaterialType on 3 columns (ProjectID,DesignMix,ContentType)

This'll be easier to run aggregates separately on the Details and MaterialType tables (via CTEs), and then join with the Master table per the requirements listed in the question:

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

And the results of running the above query:

 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

Here's a dbfiddle

NOTE: For ContentType=CT_2, I get AvgR=79.17 (79.1667 rounded), while the desired results are showing AvgR=79.16 (79.1667 truncated to 2 decimal places); depending on what's desired (rounding vs truncation) it shouldn't be too hard to tweak the query accordingly.

Related Question