display the active and last price for each item

(0) Share

Report

Posted on by On-Hand Inventory I...

120

Hi friends

I would like to display the active and last price for each item from this query select statement

I have try here to join those three tables INVENTITEMPRICE , InventDim , nventTable and actually I am not getting the correct answer from this query select statement

SELECT A.ITEMID AS ITEMID,A.NameAlias AS ItemName,B.configId AS LENGHT,B.InventColorId AS WIDTH,B.InventSizeId AS HIGHT,MAX(T1.CREATEDDATETIME) AS MAXCREATEDDATETIME,MAX(T1.ACTIVATIONDATE) AS MAXACTIVATIONDATE,
T1.VERSIONID AS VERSIONID,T1.PRICETYPE AS PRICETYPE,T1.INVENTDIMID AS INVENTDIMID,
T1.DATAAREAID AS DATAAREAID,T1.PARTITION AS PARTITION
FROM InventDim B RIGHT OUTER JOIN INVENTITEMPRICE T1 ON B.InventDimId = T1.InventDimId
RIGHT OUTER JOIN InventTable A ON A.ItemId = T1.ItemId
WHERE A.ITEMID like '4%' OR A.ITEMID like '5%'
GROUP BY A.ITEMID,A.NameAlias,B.configId,B.InventColorId,B.InventSizeId,T1.VERSIONID,T1.PRICETYPE,T1.INVENTDIMID,T1.DATAAREAID,T1.PARTITION

Best regards

Jamil

*This post is locked for comments

All responses (1)

Answers (0)

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
RE: display the active and last price for each item

Hi,

Please try the following query and let me know if anything is missing.

select distinct priceGroup.ITEMID, A.NameAlias as ItemName, B.configId, B.InventColorId, B.InventSizeId, T1.PRICE, priceGroup.MAXCREATEDDATETIME, priceGroup.MAXACTIVATIONDATE, priceGroup.VERSIONID, priceGroup.INVENTDIMID, priceGroup.PRICETYPE, T1.DATAAREAID AS DATAAREAID, T1.[PARTITION] AS [PARTITION] from InventItemPrice T1 join InventItemPriceCurrentActive priceGroup on T1.ITEMID = priceGroup.ITEMID join InventDim B on B.INVENTDIMID = T1.INVENTDIMID and B.INVENTDIMID = priceGroup.INVENTDIMID join inventTable A on A.itemid = T1.itemId where A.ITEMID like '4%' OR A.ITEMID like '5%'