Avatar of jcrozier21
jcrozier21
Flag for Australia asked on

SQL OUTER JOIN column sum returning twice the amount

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

Microsoft SQL ServerDatabasesMicrosoft Access

Avatar of undefined
Last Comment
jcrozier21

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Koen Van Wielink

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
jcrozier21

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jcrozier21

ASKER
Koen's answer was correct and helpful, but didn't suggest a solution.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy