Solved

SQL OUTER JOIN column sum returning twice the amount

Posted on 2014-11-16
3
309 Views
Last Modified: 2014-11-21
I have a display query that essentially shows stock on hand, on order, and on reserve. It's all fine aside from one part - the Sum of weight and quantity is returning doubled results (only in the case of some amount being on order). I'm sure I've stuffed my joins up, but I can't figure out how. I'd be grateful for some insight! I'll answer any replies speedily.

Current output (with doubled sum):

    PartNo                     Branch SOHWeight SOHQty ReservedQty SOOEa SOOTo AvgCost type product grade coating finish thickness width length dim1 dim2 Notes                                                                                                                                                                                                                                                            ClassFBR ClassFME ClassFSY AltoQty
    -------------------------- ------ --------- ------ ----------- ----- ----- ------- ---- ------- ----- ------- ------ --------- ----- ------ ---- ---- -----
    B-254-304---5--40-40-6000  FME    0.33      18     NULL        NULL  1.5   68.7494 B    ANGLE-E 304           NULL   5         NULL  6000   40   40   NULL                                                                                                                                                                                                                                                             C        A        B        NULL
    Warning: Null value is eliminated by an aggregate or other SET operation.

Open in new window


Expected output is exactly the same, excepting that SOHWeight and SOHQty should be halved (compared to current output)

Notes:
 - StockOnHand will have many PackNoID with the same ProductDesc (Part#)
 - There can be multiple reservations against the same PackNoID


    SELECT dbo.tblProducts.PartNo,
           'FME'                                                                                                                                                                                                AS Branch,
           SUM(sOh.Weight)                                                                                                                                                                                      AS SOHWeight,
           SUM(sOh.Quantity)                                                                                                                                                                                    AS SOHQty,
           SUM(dbo.tblReserveDetail.Quantity)                                                                                                                                                                   AS ReservedQty,
           SUM(iif(dbo.tblPurchaseOrderDetail.QuantityUnit = 'TO', NULL, dbo.tblPurchaseOrderDetail.QuantityAmount))                                                                                            AS SOOEa,
           SUM(iif(dbo.tblPurchaseOrderDetail.QuantityUnit = 'TO', dbo.tblPurchaseOrderDetail.QuantityAmount, NULL))                                                                                            AS SOOTo,
           SUM(IIF(SoH.TYPE IN ( 'C', 'SC' ), SoH.COST * SoH.WEIGHT, SoH.COST * SoH.QUANTITY) + PROCESSINGCOST + PROCESSINGFREIGHT + Packaging) / SUM(IIF(SoH.TYPE IN ( 'C', 'SC' ), sOh.weight, sOh.Quantity)) AS AvgCost,
           tblProducts.type,
           tblProducts.product,
           tblProducts.grade,
           tblProducts.coating,
           tblProducts.finish,
           tblProducts.thickness,
           tblProducts.width,
           tblProducts.length,
           tblProducts.dim1,
           tblProducts.dim2,
           tblProducts.Notes,
           tblProducts.ClassFBR,
           tblProducts.ClassFME,
           tblProducts.ClassFSY,
           SUM(IIF(SoH.STATUS = 'SC', SoH.QUANTITY, NULL))                                                                                                                                                      AS AltoQty
    FROM   DBO.tblProducts
           LEFT OUTER JOIN dbo.tblStockOnHand SoH
             ON tblProducts.PartNo = SOH.ProductDesc
                AND SoH.Status IN ( 'I', 'R', 'SC' )
                AND SoH.branch = 'FME'
           LEFT OUTER JOIN dbo.tblReserveDetail
                           RIGHT OUTER JOIN dbo.tblReserveHeader
                             ON dbo.tblReserveHeader.ID = dbo.tblReserveDetail.ReserveID
                                AND tblReserveHeader.Completed = 0
                                AND tblReserveHeader.Cancelled = 0
             ON SOH.PACKNOID = tblReserveDetail.PacknoID
           LEFT OUTER JOIN tblPurchaseOrderDetail
             ON dbo.tblPurchaseOrderDetail.ProductDesc = dbo.tblProducts.PartNo
                AND ( dbo.tblPurchaseOrderDetail.Status = N'O'
                       OR dbo.tblPurchaseOrderDetail.Status = N'PD' )
                AND tblPurchaseOrderDetail.branch = 'FME'
    GROUP  BY dbo.tblProducts.PartNo,
              tblProducts.type,
              tblProducts.product,
              tblProducts.grade,
              tblProducts.coating,
              tblProducts.finish,
              tblProducts.thickness,
              tblProducts.width,
              tblProducts.length,
              tblProducts.dim1,
              tblProducts.dim2,
              tblProducts.Notes,
              tblProducts.ClassFBR,
              tblProducts.ClassFME,
              tblProducts.ClassFSY 

Open in new window


Sample data (stripped down):
tblStockOnHand:

    PackNoID	Status	Weight	Quantity	Cost	ProcessingCost	ProcessingFreight	Packaging	Branch	tblstockonhand.ProductDesc
    157220	I	0.165	9	$68.47	$0.00	$2.48	$0.00	FME	B-254-304---5--40-40-6000
    158620	I	0.01839825	1	$64.27	$0.00	$4.72	$0.00	FSY	B-254-304---5--40-40-6000

Open in new window


tblPurchaseOrderDetail

    RecordId	Status	OrderNumber	ItemNumber	QuantityAmount	QuantityUnit	PriceQuantity	PriceUnit	Branch	ProductDesc
    6976	FD	4441	18	0.25	TO	3790	TO	FBR	B-254-304---5--40-40-6000
    6936	O	4439	23	0.5	TO	3790	TO	FME	B-254-304---5--40-40-6000
    6912	FD	4437	1	20	EA	75.96	EA	FSY	B-254-304---5--40-40-6000

Open in new window


tblProducts

    PartNo	ClassFBR	ClassFME	ClassFSY	Notes	Type	Product	Grade	Finish	Coating	Thickness	Width	Length	Dim1	Dim2
    B-254-304---5--40-40-6000	C	A	B		B	ANGLE-E	304			5		6000	40	40

Open in new window

0
Comment
Question by:jcrozier21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40446551
It's a bit tricky to sort out without the sample data of all your tables, but my gut feel says the issue is with the reservation tables (the ones for which no sample data is posted).
You state the following:

- There can be multiple reservations against the same PackNoID

That means that this join:

LEFT OUTER JOIN dbo.tblReserveDetail
                           RIGHT OUTER JOIN dbo.tblReserveHeader
                             ON dbo.tblReserveHeader.ID = dbo.tblReserveDetail.ReserveID
                                AND tblReserveHeader.Completed = 0
                                AND tblReserveHeader.Cancelled = 0
             ON SOH.PACKNOID = tblReserveDetail.PacknoID

Open in new window


Can return multiple records because it links your PackNoID from your stockOnHand to the reservations. If you happen to have 2 reservations for the same item, your stock on hand will double.
0
 
LVL 3

Assisted Solution

by:jcrozier21
jcrozier21 earned 0 total points
ID: 40446676
Finally nailed it, as far as I can tell, I have to subquery the joins before OUTER joining them, otherwise instead of grouping/summing, they will sum for each record returned. Horrible explanation I know, but here is the end result query. Thanks to anyone who had a look.

   
SELECT  dbo.tblProducts.PartNo, 'FME' AS Branch, SUM(sOh.Weight) AS SOHWeight, SUM(sOh.Quantity) AS SOHQty, 
    		ReservedQty, SOOEa,SOOTo
    		,sum(IIF(SoH.TYPE IN ('C','SC'),SoH.COST*SoH.WEIGHT,SoH.COST*SoH.QUANTITY)+soh.PROCESSINGCOST+soh.PROCESSINGFREIGHT+soh.Packaging)/SUM(IIF(SoH.TYPE IN ('C','SC'),sOh.weight,sOh.Quantity)) AS AvgCost
    		,tblProducts.type,tblProducts.product,tblProducts.grade,tblProducts.coating,tblProducts.finish,tblProducts.thickness,tblProducts.width,tblProducts.length,tblProducts.dim1,tblProducts.dim2,tblProducts.Notes,tblProducts.ClassFBR,tblProducts.ClassFME,tblProducts.ClassFSY
    		,SUM(IIF(SoH.STATUS='SC',SoH.QUANTITY,NULL)) AS AltoQty
    FROM 
    		dbo.tblProducts 
    		LEFT OUTER JOIN
    		dbo.tblStockOnHand SoH  
    		ON tblProducts.PartNo = SOH.ProductDesc AND SoH.Status IN ('I', 'R','SC') AND SoH.branch='FME' 
    		LEFT OUTER JOIN
    			(SELECT SUM(tblReserveDetail.Quantity) AS ReservedQty, ProductDesc
    			FROM dbo.tblReserveDetail INNER JOIN tblReserveHeader ON dbo.tblReserveHeader.ID = dbo.tblReserveDetail.ReserveID INNER JOIN tblStockOnHand ON tblReserveDetail.PacknoID=tblStockOnHand.PackNoID
    			WHERE tblReserveHeader.Completed=0 AND tblReserveHeader.Cancelled =0 AND BRANCH='FME'
    			GROUP BY ProductDesc) RES
    		ON SoH.ProductDesc = RES.ProductDesc 
    		LEFT OUTER JOIN 
    			(SELECT tblPurchaseOrderDetail.ProductDesc,SUM(iif(QuantityUnit='TO',null,QuantityAmount)) AS SOOEa,SUM(iif(QuantityUnit='TO',QuantityAmount,null)) AS SOOTo 
    			FROM tblPurchaseOrderDetail WHERE
    			(dbo.tblPurchaseOrderDetail.Status = N'O' OR dbo.tblPurchaseOrderDetail.Status = N'PD') AND tblPurchaseOrderDetail.branch='FME'
    			GROUP BY tblPurchaseOrderDetail.ProductDesc) PoD
    		ON PoD.ProductDesc = dbo.tblProducts.PartNo
    GROUP BY dbo.tblProducts.PartNo,tblProducts.type,tblProducts.product,tblProducts.grade,tblProducts.coating,tblProducts.finish,tblProducts.thickness,tblProducts.width,tblProducts.length,tblProducts.dim1,tblProducts.dim2,tblProducts.Notes,tblProducts.ClassFBR,tblProducts.ClassFME,tblProducts.ClassFSY,SOOEa,SOOTo,ReservedQty

Open in new window

0
 
LVL 3

Author Closing Comment

by:jcrozier21
ID: 40456966
Koen's answer was correct and helpful, but didn't suggest a solution.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question