Skip to Content
0
Jun 04, 2008 at 04:58 PM

Help with select variable on query

26 Views

I have the following Price History query which works real well except I would like to be able to add variables to select based on Oitm.ItemCode and OPLN.ListName - I have not been able to figure out where to put them. Can anyone help.

Select T1.itemcode as 'Item', (select T5.[ItemName] from oitm T5 (nolock) where T5.itemcode = T1.itemcode) as 'Description',T0.Price as 'Previous $',T1.Price as 'Current $', T0.Pricelist as 'Price List#', (select T6.[ListName] from OPLN T6 (nolock) where T6.ListNum = T0.PriceList) as 'Price List Name', T0.Factor as 'Old Factor',T0.factor as 'New Factor', (Select distinct T3.Updatedate from aitm T3 (nolock) where T3.itemcode= T0.itemcode and (T3.loginstanc +1)= T0.loginstanc ) as 'Date',T0.loginstanc as 'Log Instance', T2.listnum as 'List#',

(Select T4.base_num from opln T4 (nolock) where T4.listnum = T1.pricelist and T4.listnum = T0.pricelist) [Base PriceList ], t0.Currency,T1.CURRENCY From ait1 T0 (nolock) inner join itm1 T1 (nolock) on T0.pricelist = T1.pricelist and T0.itemcode = T1.itemcode Inner Join opln T2 on T1.pricelist = T2.listnum where

( (T0.loginstanc = ((Select count (a.Loginstanc) from ait1 a where a.itemcode = T0.itemcode and a.pricelist = T0.pricelist group by a.itemcode having count(a.Loginstanc) > '1' ) -1) and ((T1.ovrwritten='Y' and T0.Price <> T1.Price) or (T0.Price <> T1.Price and T2.base_num = T1.pricelist and T2.base_num = T1.pricelist and T2.base_Num = T2.listnum ) OR T0.CURRENCY <> T1.CURRENCY)) or (T0.loginstanc = (Select top 1 c.loginstanc from ait1 c where c.itemcode = T0.itemcode and c.pricelist = T0.pricelist and c.loginstanc <> '1' order by c.loginstanc desc) and T2.base_num <> T1.pricelist and T2.base_num <> T1.pricelist and T2.base_Num <> T2.listnum and ((T0.Factor <> T1.factor) or (T1.ovrwritten = 'Y' and T0.Price <> T1.Price) or T0.price <> T1.price OR T0.CURRENCY <> T1.CURRENCY))) and (T0.Price >= 0 or T1.Price >=0) and ((T0.Price <> T1.Price) or ((T0.Price <> (Select distinct b.pricelist from itm1 b right outer join OPLN T4 on b.pricelist= T4.listnum and b.pricelist <> T4.base_Num where b.pricelist = T1.pricelist and b.itemcode =T1.itemcode )))) order by T1.itemcode