2

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

Improve this question
2
  • 4
    what query(s) have you tried so far? what are the PKs for each table?
    – markp-fuso
    Commented Nov 13, 2017 at 17:03
  • @markp I have updated the question and the PKs for each table.
    – SQLserving
    Commented Nov 13, 2017 at 17:50

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • this worked. Thanks! The answer by Ed Mendez also works but I am using this because I was able to manipulate the query quicker to what I wanted.
    – SQLserving
    Commented Nov 14, 2017 at 13:34
  • 1
    my query includes the ContentType column in the group by and join clauses related to the MaterialType table, while Ed's query is missing the ContentType column in the group by and join clauses; not taking anything away from Ed's query, but you you may want to re-review your requirements and the two answers to determine what to do with the ContentType column; yes, the two queries return the same answer for the given set of sample data, but the two queries likely won't return the same data for all of your real-world data
    – markp-fuso
    Commented Nov 14, 2017 at 13:41
1

Try this query

WITH cte_sum
AS (
    SELECT ProjectID
        ,DesignMix
        ,Max(Material) Material
        ,count(DISTINCT Material) ct
    FROM MaterialType
    GROUP BY ProjectID
        ,DesignMix
    )
    ,cte_dtl
AS (
    SELECT ProjectID
        ,count(Length) CountLength
        ,Sum(Length) SumLength
        ,Avg(TenthReading) AvgR
        ,Min(TenthReading) MinR
        ,Max(TenthReading) MaxR
        ,Sum(CASE WHEN TenthReading > 95 THEN 1 ELSE 0 END) CountRGreater95
        ,Sum(CASE WHEN TenthReading > 95 THEN Length ELSE 0 END) SumLengthGreater95
    FROM Details
    GROUP BY ProjectID
    )
SELECT M.ContentType
    ,CASE WHEN cte_sum.ct > 1 THEN 'Mixed' ELSE cte_sum.material END Material
    ,D.CountLength
    ,D.SumLength
    ,D.AvgR
    ,D.MinR
    ,D.MaxR
    ,D.CountRGreater95
    ,D.SumLengthGreater95
FROM Master M
LEFT OUTER JOIN cte_sum ON (
        M.ProjectID = cte_sum.ProjectID
        AND M.DesignMix = cte_sum.DesignMix
        )
LEFT OUTER JOIN cte_dtl D ON (M.ProjectID = D.ProjectID)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.