0

I am trying to sum the number of material and place it with the parts produced in a shift. My thought was to use a left join to do what I want to do but I am doing something wrong. I have included a snippet of the data I am pulling information from.

Table 01 

Location    Shift   Prod_Date   Total_Parts_Produced
Converter021    1   9/1/2017    270
Converter023    3   9/4/2017    300
Converter021    1   9/5/2017    500
Converter023    3   9/5/2017    486
Converter021    1   9/6/2017    300
Converter023    3   9/6/2017    486
Converter021    1   9/7/2017    500
Converter023    3   9/10/2017   600
Converter021    1   9/11/2017   600
Converter023    3   9/11/2017   486
Converter021    1   9/12/2017   630
Converter023    3   9/12/2017   486
Converter021    1   9/13/2017   500
Converter023    3   9/13/2017   600
Converter021    1   9/14/2017   391
Converter023    3   9/14/2017   500

Table 02 

Location    Date_Created    Quantity
Converter021    9/1/2017    39
Converter021    9/1/2017    20
Converter021    9/1/2017    13
Converter021    9/1/2017    3
Converter021    9/1/2017    39
Converter021    9/9/2017    59
Converter021    9/11/2017   1
Converter021    9/11/2017   3
Converter021    9/12/2017   13
Converter021    9/12/2017   21
Converter021    9/14/2017   3
Converter021    9/14/2017   12
Converter021    9/14/2017   2
Converter023    9/15/2017   38
Converter023    9/15/2017   36
Converter023    9/15/2017   116 


SELECT
    si.Location
    ,si.Shift
    ,si.Prod_Date
    ,si.Total_Parts_Produced AS 'Parts Produced'
    ,SUM(b.Quantity)         AS 'Total'
FROM Table01      si
LEFT JOIN Table02 a ON si.Location = a.Location
LEFT JOIN Table02 b ON si.Prod_Date = b.Date_Created
WHERE Line = 'Converter 2'
    AND Prod_Date >= '09-01-2017'
    AND Prod_Date <= '09-17-2017'
GROUP BY Prod_Date
    ,si.Work_Center
    ,Shift
    ,Total_Parts_Produced
ORDER BY Prod_Date
    ,Shift ASC;

Expected Outcome 

Location    Shift   Prod_Date   Total_Parts_Produced    Total
Converter021    1   9/1/2017    270         114
Converter023    3   9/4/2017    300         0
Converter021    1   9/5/2017    500         0
Converter023    3   9/5/2017    486         0
Converter021    1   9/6/2017    300         0
Converter023    3   9/6/2017    486         0
Converter021    1   9/7/2017    500         0
Converter023    3   9/10/2017   600         0
Converter021    1   9/11/2017   600         4
Converter023    3   9/11/2017   486         0
Converter021    1   9/12/2017   630         34
Converter023    3   9/12/2017   486         0
Converter021    1   9/13/2017   500         0
Converter023    3   9/13/2017   600         0
Converter021    1   9/14/2017   391         17
Converter023    3   9/14/2017   500         0
Converter023    3   9/17/2017   600         0

This is what I get when I run my query. I have not been able to figure out what is wrong.

Code Results

Location    Shift   Prod_Date   Total_Parts_Produced    Total
Converter021    1   9/1/2017    270         2853600
Converter023    3   9/4/2017    300         NULL
Converter021    1   9/5/2017    500         1000500
Converter023    3   9/5/2017    486         92000
Converter021    1   9/6/2017    300         533745
Converter023    3   9/6/2017    486         49080
Converter021    1   9/7/2017    500         566805
Converter023    3   9/10/2017   600         800
Converter021    1   9/11/2017   600         765600
Converter023    3   9/11/2017   486         70400
Converter021    1   9/12/2017   630         691215
Converter023    3   9/12/2017   486         63560
Converter021    1   9/13/2017   500         622485
Converter023    3   9/13/2017   600         57240
Converter021    1   9/14/2017   391         1424625
Converter023    3   9/14/2017   500         131000
Converter023    3   9/17/2017   600         NULL
digital.aaron
  • 5,435
  • 2
  • 24
  • 43
davtur
  • 1
  • Thank you @digital.arron for correcting the formatting. I was trying to delete the post and correct the formatting once I saw the post. – davtur Dec 11 '17 at 15:45
  • Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) – philipxy Dec 11 '17 at 23:59
  • Either left join applied to the original would get you ready to group & aggregate for certain columns. But here you are blindly left joining without thinking about the effect of the first on the second. Look at the output from the first & you will see you do not want the 2nd left join of it, you want something else. Ie a join of the 2 individual left joins. – philipxy Dec 12 '17 at 00:04
  • Thank you @philipxy. I am looking at link in your above post and seeing if I can use any of the comments within it. – davtur Dec 13 '17 at 16:01

0 Answers0