SELECT L0.ItemCode , L0.Dscription , L0.Quantity , L0.Rate ,
ROUND(L0.PriceFOB,2) [FOB PRICE], ROUND(L0.CUSTOM,2) [CUSTOM PER UNIT] , ROUND(L0.COST,2) [EXPNS. PER UNIT] ,
ROUND(L0.PRICEATWH ,2) [CURRENT LP] , L1.DOCDATE [CURRENT LP DATE] ,L2.PriceAtWH [LAST LP],
G1.DocEntry [CURRENT LP #], G1.DocNum , L1.SUPPNAME ,
L2.[LAST LP #] , L2.DocDate [LAST LP DATE]
FROM IPF1 L0 INNER JOIN OIPF L1 ON L1.DOCENTRY = L0.DOCENTRY
(SELECT DISTINCT OPDN.DOCENTRY , OPDN.DocNum FROM OPDN INNER JOIN IPF1 ON OPDN.DocEntry = IPF1.BaseEntry )
G1 ON G1.DOCENTRY = L0.BASEENTRY
(SELECT (IPF1.DOCENTRY) [LAST LP #], IPF1.CardCode , IPF1.ITEMCODE , IPF1.Dscription , IPF1.PriceAtWH , OIPF.DOCDATE
FROM IPF1 INNER JOIN OIPF ON OIPF.DocEntry = IPF1.DOCENTRY
GROUP BY IPF1.DOCENTRY , IPF1.CardCode , IPF1.ITEMCODE , IPF1.Dscription , IPF1.PriceAtWH , OIPF.DOCDATE )
L2 ON L2.ItemCode = L0.ItemCode AND L2.DocDate < L1.DocDate
AND L0.CardCode = L2.CARDCODE AND L0.DocEntry <> L2.[LAST LP #]
WHERE L1.DocEntry = 2014
I've framed a query wherein I want two landed costs for the items to be compared.
But the last join of my query is fetching multiple rows for the item. Whereas I only want last LP.
for eg. product X has come on dec 12 , nov 12 , sep 12 and jun 12
i 'm printing landed cost details of dec 12 and for comparison against that only nov 12 should get printed b'cos it is the immediate last landed cost.
but it is giving me sep as well as jun 12 details.
Please help me re - framing.