Get Correct Price based on Effectivity Date

Question

Hi,

I have a problem getting the right "Price" for a product based on Effectivity date.

Example, I have 2 tables:

a. "Transaction" table --> this contains the products ordered, and
b. "Item Master" table --> this contains the product prices and effectivity dates of those prices

Inside the Trasaction table:

INVOICE_NO INVOICE_DATE PRODUCT_PKG_CODE PRODUCT_PKG_ITEM 1234 6/29/2009 ProductA ProductA-01 1234 6/29/2009 ProductA ProductA-02 1234 6/29/2009 ProductA ProductA-03 Inside the "Item_Master" table: PRODUCT_PKG_CODE PRODUCT_PKG_ITEM PRODUCT_ITEM_PRICE EFFECTIVITY_DATE ProductA ProductA-01 25 6/1/2009 ProductA ProductA-02 22 6/1/2009 ProductA ProductA-03 20 6/1/2009 ProductA ProductA-01 15 5/1/2009 ProductA ProductA-02 12 5/1/2009 ProductA ProductA-03 10 5/1/2009 ProductA ProductA-01 19 4/1/2009 ProductA ProductA-02 17 4/1/2009 ProductA ProductA-03 15 4/1/2009 In my report, I need to display the Invoices and Orders, as well as the Price of the Order Item which was effective at the time it was paid (Invoice Date). My query looks like this (my source db is Oracle):

SELECT   T.INVOICE_NO,
     T.INVOICE_DATE,
     T.PRODUCT_PKG_CODE,
     T.PRODUCT_PKG_ITEM,
     P.PRODUCT_ITEM_PRICE    FROM   TRANSACTION T,
     ITEM_MASTER P    WHERE   T.PRODUCT_PKG_CODE = P.PRODUCT_PKG_CODE
     AND T.PRODUCT_PKG_ITEM = P.PRODUCT_PKG_ITEM
     AND P.EFFECTIVITY_DATE <= T.INVOICE_DATE
     AND T.INVOICE_NO = '1234';

...which shows 2 prices for each item.
I did some other different query styles but to no avail, so I decided it's time to get help. :)
Thanks to any of you who can share your knowledge. --CJ--

p.s. Sorry, my post doesn't even look right! :D

Answer 1

If it's returning two rows with different effective dates that are less than the invoice date, you may want to change your date join to

    'AND T.INVOICE_DATE = (
select max(effectivity_date) 
from item_master 
where effectivity_date < t.invoice_date)'

or something like that, to only get the one price that is the most recent one before the invoice date.

Answer 2

While your question's formatting is a bit too messy for me to get all the details, it sure does look like you're looking for the standard SQL construct ROW_NUMBER() OVER with both PARTITION and ORDER_BY -- it's in PostgreSql 8.4 and has been in Oracle [and MS SQL Server too, and DB2...] for quite a while, and it's the handiest way to select the "top" (or "top N") "by group" and with a certain order of anything in a SQL query. Look it up, see here for the PosgreSQL-specific docs.

Answer 3

Analytics is your friend. You can use the FIRST_VALUE() function, for example, to get all the product_item_prices for the given product, sort by effectivity_date (descending), and just pick the first one. You'll need a DISTINCT as well so that only one row is returned for each transaction.

SELECT   DISTINCT
     T.INVOICE_NO,
     T.INVOICE_DATE,
     T.PRODUCT_PKG_CODE,
     T.PRODUCT_PKG_ITEM,
     FIRST_VALUE(P.PRODUCT_ITEM_PRICE)
       OVER (PARTITION BY T.INVOICE_NO, T.INVOICE_DATE,
                         T.PRODUCT_PKG_CODE, T.PRODUCT_PKG_ITEM
            ORDER BY P.EFFECTIVITY_DATE DESC)
        as PRODUCT_ITEM_PRICE
FROM   TRANSACTION T,
     ITEM_MASTER P    
WHERE   T.PRODUCT_PKG_CODE = P.PRODUCT_PKG_CODE
     AND T.PRODUCT_PKG_ITEM = P.PRODUCT_PKG_ITEM
     AND P.EFFECTIVITY_DATE <= T.INVOICE_DATE
     AND T.INVOICE_NO = '1234';