Solved

SQL OUTER JOIN column sum returning twice the amount

Posted on 2014-11-16
3
273 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
  • 2
3 Comments
 
LVL 12

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now