CODEWITHSUNDEEP
sqlsql-server
paulpitchford
paulpitchford

Reputation: 560

SQL: Select data from Parent table and sum columns from two Children tables in output

I have a parent table that has two tables of child data as well as a couple of referencing tables. (Only relevant data shown for simplicity)

enter image description here

Sample Data: ShiftAnalysis

+----+------------+-----+
| Id | StartTime  | Hrs |
+----+------------+-----+
|  1 | 2018-02-01 |   8 |
|  2 | 2018-02-02 |   8 |
|  3 | 2018-02-03 |   8 |
|  4 | 2018-02-04 |  12 |
+----+------------+-----+

Sample Data: ShiftAnalysisItems

+----+-----------+---------+---------------+------------+
| Id | SetupTime | RunTime | ShiftAnalysis | SalesOrder |
+----+-----------+---------+---------------+------------+
|  1 |        60 |     400 |             1 |       7018 |
|  2 |        30 |     450 |             2 |       7019 |
|  3 |        60 |     120 |             3 |       7020 |
|  4 |        60 |     240 |             3 |       7021 |
|  5 |        15 |     195 |             4 |       7022 |
|  6 |        15 |     195 |             4 |       7022 |
+----+-----------+---------+---------------+------------+

Sample Data: ShiftAnalysisDownTimeItems

+----+------------------+---------------+------------+
| Id | DownTimeDuration | ShiftAnalysis | SalesOrder |
+----+------------------+---------------+------------+
|  1 |               20 |             1 |       7018 |
|  2 |               30 |             4 |       7022 |
|  3 |               30 |             4 |       7022 |
+----+------------------+---------------+------------+

What I'd like to do is Select:

ShiftAnalysis.StartTime
ShiftAnaylsys.ManHours
Employee.Name
SUM(ShiftAnalysisItems.RunTime)
SUM(ShiftAnalysisItems.SetupTime)
SUM(ShiftAnalysisDownTimeItems.DownTimeDuration)

I have created this T-SQL:

Select 
    StartTime, 
    ManHours,
    emp.Name as Employee,
    (Select Sum(RunTime) from ShiftAnalysisItems as sai join ShiftAnalysis as sa1 on sai.ShiftAnalysisItem_ShiftAnalysis = sa1.Id where sa1.Id = sa.Id) as RunTime,
    (Select Sum(SetupTime) from ShiftAnalysisItems as sai join ShiftAnalysis as sa1 on sai.ShiftAnalysisItem_ShiftAnalysis = sa1.Id where sa1.Id = sa.Id) as SetupTime,
    (Select Sum(DownTimeDuration) from ShiftAnalysisDownTimeItems as dti join ShiftAnalysis as sa1 on dti.ShiftAnalysisDownTimeItem_ShiftAnalysis = sa1.Id where sa1.Id = sa.Id) as DownTime
from 
    ShiftAnalysis as sa
join 
    Employees as emp on sa.ShiftAnalysis_Employee = emp.Id
join 
    Machines as m on sa.ShiftAnalysis_Machine = m.Id
join 
    MachineTypes as mt on m.Machine_MachineType = mt.Id
Where
    mt.Description = 'Print'
Order By
    StartTime

This is a snippet of the output:

+------------+----------+------------+---------+-----------+----------+
| StartTime  | ManHours |  Employee  | RunTime | SetupTime | DownTime |
+------------+----------+------------+---------+-----------+----------+
| 2018-01-02 | 12.00    | Employee 1 | 190.00  | 215.00    |      315 |
| 2018-01-02 | 7.50     | Employee 2 | NULL    | NULL      |      450 |
| 2018-01-02 | 7.50     | Employee 3 | NULL    | NULL      |      450 |
| 2018-01-02 | 9.00     | Employee 4 | 170.00  | 70.00     |      300 |
| 2018-01-02 | 9.00     | Employee 4 | 170.00  | 70.00     |      300 |
| 2018-01-03 | 7.50     | Employee 2 | 165.00  | 100.00    |      185 |
+------------+----------+------------+---------+-----------+----------+

My problem is that it only seemingly shows records where there is downtime. Not every ShiftAnalysis record has DowTime Children.

Also, it looks like I need to group those inline selects because as you can see with Employee 4 above, it shows it twice. I've tried grouping the inline queries but I just cannot get my head around it and nothing seem favourable so far. Hopefully someone can point me in the right direction? Thanks.

Upvotes: 2

Views: 920

Answers (1)

user4219031
user4219031

Reputation: 307

Please check the query below, try LEFT OUTER JOIN instead if sub-queries:

SELECT 
    StartTime, 
    ManHours,
    emp.Name AS Employee,
    SUM(ISNUL(RunTime,0)) AS RunTime,
    SUM(ISNUL(SetupTime,0)) AS SetupTime,
    SUM(ISNUL(DownTimeDuration,0)) AS DownTime
FROM 
    ShiftAnalysis AS sa
INNER JOIN
    Employees AS emp ON sa.ShiftAnalysis_Employee = emp.Id
INNER JOIN
    Machines AS m ON sa.ShiftAnalysis_Machine = m.Id
INNER JOIN
    MachineTypes AS mt ON m.Machine_MachineType = mt.Id
LEFT OUTER JOIN 
    ShiftAnalysisItems AS sai ON sa.Id = sai.ShiftAnalysisItem_ShiftAnalysis
LEFT OUTER JOIN 
    ShiftAnalysisDownTimeItems  AS dti ON sa.Id = dti.ShiftAnalysisDownTimeItem_ShiftAnalysis
WHERE
    mt.Description = 'Print'
GROUP BY 
    StartTime, ManHours,  emp.Name 
ORDER BY
    StartTime

Upvotes: 2

Related Questions