troubleshooting Question

SQL OUTER JOIN column sum returning twice the amount

Avatar of jcrozier21
jcrozier21Flag for Australia asked on
DatabasesMicrosoft AccessMicrosoft SQL Server
3 Comments2 Solutions444 ViewsLast Modified:
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.

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 

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

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

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
ASKER CERTIFIED SOLUTION
Koen Van Wielink
Business Intelligence Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros