Base Price and Special Price Query

[Former Member]

Hi,,

I need a Query where the results will be Item Code, Base Price(Item Cost) and Special Price from where I will calculate Profit Amount ,conditions being  Price List, Warehouse(For Item Cost),Customer Code(Item master UDF).Special Price should be the least Price of the Quantity Scale Special Price (ex,for 100 qty price-10 rs,120qty -8rs,130qty-7rs.The Special price of the query should be 7rs.)

Thanks and Regards,

S.G.Sekar

[former_member325312]

Hi Sekar

Find the query below

SELECT T0.[DocNum], T0.[CardCode],T1.[Price], T1.[Quantity],

CASE when T1.[Quantity]  = '100' then '100'

when T1.[Quantity]  = '200' then '80'

when T1.[Quantity]  >= '300' then '70' else  T1.[Price] End as 'Spcl Price',

T1.[Price], T3.[AvgPrice]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

left join  OSPP T2 on t2.itemcode = t1.itemcode and t2.cardcode = t0.cardcode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode

WHERE T2.[ListNum]  = '[%0]' and  T4.[WhsCode]  = '[%1]' and  T2.[CardCode] = '[%2]'

Regards

Jenny

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[DocNum], T2.[CardCode], T1.[Price], T3.[AvgPrice] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry left join  OSPP T2 on t2.itemcode = t1.itemcode and t2.cardcode = t0.cardcode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode WHERE T2.[ListNum]  = [%0] and  T4.[WhsCode]  = [%1] and  T2.[CardCode] = [%2]

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

Thanks for your reply.But the Query shows the special price for the higher quantity.But I want special price for the least quantity.In the Quantity Scale Special Price for Customers,we are having quantity wise Price .For 100 nos the special Price is 100 rs.For 200 nos price is 80 and for 300 nos 70.Now the query shows price as 100 Rs.But I want 70 rs as the Query Result.

Thanks and Regards,

S.G.Sekar

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[CardCode], T2.[ItemCode], T2.[ItemName], T2.[AvgPrice], T1.[Amount], min(T1.[Price]) FROM OSPP T0 left join  SPP2 T1 on t0.itemcode = t1.itemcode left JOIN OITM T2 ON T0.ItemCode = T2.ItemCode left JOIN OITW T3 ON T2.ItemCode = T3.ItemCode WHERE T0.[ListNum] = [%0] and  T3.[WhsCode] = [%1] and  T0.[CardCode] = [%3] GROUP BY T0.[CardCode], T2.[ItemCode], T2.[ItemName], T2.[AvgPrice], T1.[Amount] having min(T1.[Price]) <=70

Thanks & Regards,

Nagarajan