Struggling with SQL view duplicating data

Hello, i have a sql view that is duplicating data on most of / or some of the rows and i’ am finding hard to out what the issue is - any help would be any help would be greatly appreciated

SQL view -

SELECT TOP (100) PERCENT dbo.PartTran.PartNum, dbo.Part.PartDescription, dbo.PartTran.TranType, dbo.PartTran.TranQty, dbo.PartTran.TranDate, dbo.Customer.CustID,
dbo.Customer.Name, dbo.OrderRel.OrderNum, dbo.OrderRel.OrderLine, dbo.OrderRel.OrderRelNum
dbo.OrderDtl INNER JOIN
dbo.Customer INNER JOIN
dbo.OrderHed ON dbo.Customer.CustNum = dbo.OrderHed.CustNum AND dbo.Customer.Company = dbo.OrderHed.Company ON
dbo.OrderDtl.Company = dbo.OrderHed.Company AND dbo.OrderDtl.OrderNum = dbo.OrderHed.OrderNum INNER JOIN
dbo.OrderRel ON dbo.OrderDtl.Company = dbo.OrderRel.Company AND dbo.OrderDtl.OrderNum = dbo.OrderRel.OrderNum AND
dbo.OrderDtl.OrderLine = dbo.OrderRel.OrderLine ON dbo.PartTran.OrderRelNum = dbo.OrderRel.OrderRelNum AND
dbo.PartTran.OrderLine = dbo.OrderRel.OrderLine AND dbo.PartTran.OrderNum = dbo.OrderRel.OrderNum AND dbo.PartTran.Company = dbo.OrderRel.Company ON
dbo.Part.Company = dbo.PartTran.Company AND dbo.Part.PartNum = dbo.PartTran.PartNum
WHERE (dbo.PartTran.TranType LIKE ‘stk-cus’ OR
dbo.PartTran.TranType LIKE ‘mfg-cus’) AND (NOT (dbo.PartTran.PartNum LIKE ‘DEL’)) AND (dbo.PartTran.TranQty > 0)
ORDER BY dbo.OrderRel.OrderNum, dbo.OrderRel.OrderLine, dbo.OrderRel.OrderRelNum


What are you trying to accomplish?

Hi Mark,

This view is a how items we have sold to and i off load it into crystal repot, because of the duplication in the view the figure are not correct in my report so its saying i sold 440 when i sold 120.

I checked the data in epicor and yes we sold 120 not 440 so the SQL view has issues.

Thanks in advanced.

Check your PartTran records. Anytime a Customer Shipment is checked as Shipped or UnShipped, a PartTran record is created. This would cause duplicates since you are only selecting TranQty > 0.

You may want to try an order change in how you are selecting the data. I think your “where” clause is tight enough because you are filtering by the trantype. You may want to also try a sub-query if you are building the view directly on the SQL server. I left out all of the company links, but you know to add those it looks like.

left outer join
on parttran.ordernum = orderrel.ordernum and parttran.orderline = orderrel.orderline
and parttran.orderrelnum = orderrel.orderrelnum
left outer join
on orderrel.ordernum = orderdtl.ordernum and orderrel.orderline = orderdtl.orderline
left outer join
on orderdtl.ordernum = orderhed.ordernum
left outer join
on orderhed.custnum = customer.custnum
left outer join
on parttran.partnum = part.partnum

When do you consider an item sold? Once it’s on an Order? Once it’s shipped? Once it’s Invoiced?

For the reasons @bmgarver gave, I would use InvcDtl or ShipDtl records to make your life much simpler.

Mark W.

good question mark, i would of said once shipped.