query

[Former Member]

Hi there,

I do need to extract price information out of the system.

Needed:

query which lists period and volume discount prices and customer discounted prices.

So cardcode, itemcode, customer discount prices & general period and volume discount prices (if there are against items)

any idea ?

thanks

Mike

[KennedyT21]

Try This

SELECT

     T0.ItemCode,

     T0.ItemName,

     'Default Price List' = TPL.ListName,

     'Special Price per BP' = ROUND(T1P.Price * (- T1.Discount / 100 + 1), 2)

     'Default Price' = T0P.Price

FROM

     OCRD TBP

     FULL OUTER JOIN OITM T0 ON 1=1

     LEFT JOIN OSPP T1 ON T0.ItemCode = T1.ItemCode AND T1.CardCode = TBP.CardCode

     LEFT JOIN ITM1 T1P ON T0.ItemCode = T1P.ItemCode AND T1.ListNum = T1P.PriceList

     LEFT JOIN ITM1 T0P ON T0.ItemCode = T0P.ItemCode AND T0P.PriceList = TBP.ListNum

     LEFT JOIN OPLN TPL ON TBP.ListNum = TPL.ListNum

WHERE

     TBP.CardCode = '[%0]'

     AND T0.SellItem = 'Y'

ORDER BY

     T0.ItemCode

Rgds

[Former Member]

thanks for your prompt response Kennedy well ideally I need to see something which compares my customer specific customer prices with item specific period and volume discount prices started with this one below which is wrong: SELECT T0.CardCode, T0.ItemCode, T0.Price, T1.Price, T2.Price, T5.Price FROM OCRD T3 LEFT OUTER JOIN OSPP T0 ON T3.CardCode = T0.CardCode LEFT OUTER JOIN SPP1 T1 ON T0.CardCode = T1.CardCode AND T0.ItemCode = T1.ItemCode LEFT OUTER JOIN SPP2 T2 ON T0.CardCode = T2.CardCode AND T0.ItemCode = T2.ItemCode LEFT OUTER JOIN SPP1 T4 ON LEFT OUTER JOIN SPP2 T5 ON T4.CardCode = T5.CardCode AND T4.ItemCode = T5.ItemCode

[Johan_H]

Hi Michael,

Please give this a try:

SELECT c.CardCode

     , p0.ItemCode

     , CAST(p0.Price AS MONEY) AS DiscountedPrice_Base

     , ISNULL(T1.FromDate, DATEADD(YEAR, -99, GETDATE())) AS PeriodFrom

     , ISNULL(T1.ToDate, DATEADD(YEAR, 99, GETDATE())) AS PeriodTo

     , CAST(COALESCE(T1.Price, p0.Price, 0) AS MONEY) AS DiscountedPrice_InPeriod

     , ISNULL(T2.Amount, 1) AS MinimumVolume

     , CAST(COALESCE(T2.Price, T1.Price, p0.Price, 0) AS MONEY) AS DiscountedPrice_Volume_InPeriod

FROM OCRD c

LEFT OUTER JOIN OSPP p0 ON c.CardCode = p0.CardCode

LEFT OUTER JOIN SPP1 T1 ON c.CardCode = T1.CardCode AND p0.ItemCode = T1.ItemCode

LEFT OUTER JOIN SPP2 T2 ON c.CardCode = T2.CardCode AND T1.LINENUM = T2.[SPP1LNum]

WHERE ISNULL(c.CardCode, '') = [%0]

Regards,

Johan

[Former Member]

thanks Johan

any chance we can have the period and volume discount listed in the last column? (Stock Management > Price List > Period and Volume Discount)

[Johan_H]

No problem, please try it with this SELECT clause:

SELECT c.CardCode

     , p0.ItemCode

     , ISNULL(p0.Discount, 0) AS 'Base Discount %'

     , CAST(p0.Price AS MONEY) AS 'Base Discounted Price'

     , ISNULL(T1.FromDate, DATEADD(YEAR, -99, GETDATE())) AS 'Period From'

     , ISNULL(T1.ToDate, DATEADD(YEAR, 99, GETDATE())) AS 'Period To'

     , ISNULL(T1.Discount, 0) AS 'Period Discount %'

     , CAST(COALESCE(T1.Price, p0.Price, 0) AS MONEY) AS 'Period Discounted Price'

     , ISNULL(T2.Amount, 1) AS 'Minimum Volume'

     , ISNULL(T2.Discount, 0) AS 'Volume Discount %'

     , CAST(COALESCE(T2.Price, T1.Price, p0.Price, 0) AS MONEY) AS 'Volume Discounted Price In Period'

Regards,

Johan