CODEWITHSUNDEEP
plsql
CJ.
CJ.

Reputation: 27

Get Correct Price based on Effectivity Date

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

Upvotes: 2

Views: 264

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

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';

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 882421

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.

Upvotes: 1

Lazy Bob
Lazy Bob

Reputation: 449

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.

Upvotes: 2

Related Questions