cancel
Showing results for 
Search instead for 
Did you mean: 

help in query

former_member218051
Active Contributor
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi Malhaar,

Please try this:

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 , MAX(OIPF.DOCDATE) AS DocDate

FROM IPF1 INNER JOIN OIPF ON OIPF.DocEntry = IPF1.DOCENTRY

GROUP BY IPF1.DOCENTRY , IPF1.CardCode , IPF1.ITEMCODE , IPF1.Dscription , IPF1.PriceAtWH)

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

Regards,

Johan