Skip to Content
avatar image
Former Member

query

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



Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Mar 29, 2016 at 08:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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