SQL joins for quote to invoice cycle report

I am getting dup values from my query below. Can someone advise if my table joins are incorrect or missing a key field join. Thank you. I am trying to bring in everything from quote to order, order to job, and order/job to invoice for tracking turnover times and locations ect. Thank you.

select

qh.QuoteNum, qd.QuoteLine,qh.EntryDate,qh.ClosedDate as quote_closeddate,qh.QuoteClosed,qd.DocExtPriceDtl as quote_total,
jh.JobNum,jh.JobClosed,jh.Date01,jh.CreateDate,jh.ClosedDate job_closeddate,
od.OrderNum, od.OrderLine,oh.OrderDate,od.DocExtPriceDtl as order_total,
orel.Make,
id.InvoiceNum, ih.ClosedDate, id.InvoiceLine,ih.OpenInvoice,ih.InvoiceDate,id.ShipDate,id.DocExtPrice as invoice_total

from
QuoteHed qh
join QuoteDtl qd
on qd.QuoteNum = qh.QuoteNum
left join
OrderDtl od
on od.QuoteNum = qd.QuoteNum
and od.QuoteLine = qd.QuoteLine
left join
OrderRel orel
on orel.Ordernum = od.OrderNum
and orel.Orderline = od.Orderline
left join
OrderHed oh
on oh.OrderNum = od.OrderNum
left join
InvcDtl id
on id.OrderNum = od.OrderNum
and id.OrderLine = od.OrderLine
left join Erp.InvcHead ih
on ih.InvoiceNum = id.InvoiceNum
left join
JobProd jp
on jp.OrderNum = od.OrderNum
and jp.OrderLine = od.OrderLine
left join
JobHead jh
on jh.JobNum = jp.JobNum

Joining that many areas together you are bound to get “repeating”. I think you are fine in this case, as long as you handle it appropriately in the report side of things.

Joining Orders to Invoices will give you repeating because you can have multiple invoices per order. You can have multiple jobs per order as well. If its causing issues when you lay it out, then you need to rethink your query and take some areas out. Subreports are a less performant, but easy way to avoid repeating data.

All joins are missing join by company field. This field is part of primary key and its missing usually is not good for performance

1 Like