Need to add logic to SQL join
HI I have a union SQL that joins Current and History records.
This works fine except there are 2 quirks that have to be accounted for. I would like to account for this in the SQL rather than the reporting level.
1. If an item appears 2 times on the same SOP document it won't get counted onto the report.
2. Averages are made if items are purchased multiple times at differing
prices.
The SQL:
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source
FROM ((((PBS.dbo.SOP10200
SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))
INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR
UNION ALL
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' as source
FROM ((((PBS.dbo.SOP30300
SOP30300 LEFT OUTER JOIN PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))
LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR
