on 06-06-2013 11:15 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.