Skip to Content
0
Jun 06, 2013 at 10:15 AM

help in query

27 Views

Hello Experts,

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

INNER JOIN

(SELECT DISTINCT OPDN.DOCENTRY , OPDN.DocNum FROM OPDN INNER JOIN IPF1 ON OPDN.DocEntry = IPF1.BaseEntry )

G1 ON G1.DOCENTRY = L0.BASEENTRY

LEFT JOIN

(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.

Thanking You

Malhaar