derekmcdonnell3 (10/24/2013)
I made a mistake, it is in the ON statement that must be conditional, and only on that one field. The data is too messy to get a good sample, but here is the codeSELECT p.Warehouse, p.PartNo, COUNT(w.WONo) AS OrderCount, SUM(w.Qty) AS QtySold, MAX(EntryDate) AS LastSaleDate, WONo
FROM (SELECT WONo, EntryDate, Qty, BillTo, PartNo, PartNoAlias
FROM woparts
WHERE Left(billto,1)='t' and EntryDate > '2012-10-16' and WONo<910000000) w
right join
(SELECT *
FROM parts
WHERE Left(Warehouse,1)='t' and OnHand > 0) p
ON (p.partno=w.partno or w.PartNo = p.PartNoOriginal or w.partno = p.PartNoAlias or w.PartNo = p.OldNUmber or if(p.PartNoAlias='',,w.PartNoAlias=p.PartNoAlias)) and p.Warehouse= left (w.BillTo,5)
GROUP BY p.Warehouse, p.PartNo, WONo
Try rewriting it in a more conventional manner, something like this:
SELECT
p.Warehouse,
p.PartNo,
COUNT(w.WONo) AS OrderCount, -- will always be 1 because w.WONo is in group by
SUM(w.Qty) AS QtySold,
MAX(w.EntryDate) AS LastSaleDate,
WONo
FROM parts p
LEFT JOIN woparts w
ON (w.partno IN (p.partno, p.PartNoOriginal, p.PartNoAlias, p.OldNUmber)
or (p.PartNoAlias = '' AND w.PartNoAlias = ''))
and p.Warehouse = left(w.BillTo,5)
AND Left(w.billto, 1) = 't'
and w.EntryDate > '2012-10-16'
and w.WONo < 910000000
WHERE Left(p.Warehouse,1) = 't'
and p.OnHand > 0
GROUP BY p.Warehouse, p.PartNo, w.WONo
This is much easier for most folks to understand. If they understand your code, you've got a better chance of them helping you.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden