根据有效日期获得正确的价格

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

根据有效日期,我在为产品获得正确的“价格”时遇到问题。

Example, I have 2 tables:

例如,我有2个表:

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

一个。 “交易”表 - >这包含订购的产品,以及b。 “物料主数据”表 - >这包含这些价格的产品价格和有效日期

Inside the Trasaction table:

Trasaction表内:

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

在“Item_Master”表中: 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):

在我的报告中,我需要显示发票和订单, 以及有效的订单项目的价格在付款时(发票日期)。我的查询看起来像这样(我的源数据库是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--

...显示每个项目的2个价格。我做了一些其他不同的查询样式,但没有用,所以我决定是时候得到帮助了。 :)感谢任何能分享您知识的人。 --CJ--

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

附:对不起,我的帖子看起来甚至不对! :d

3 个解决方案

#1

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.

分析是你的朋友。例如,您可以使用FIRST_VALUE()函数获取给定产品的所有product_item_prices,按effectivity_date(降序)排序,然后选择第一个。您还需要一个DISTINCT,以便每个事务只返回一行。

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

#2

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.

或类似的东西,只能获得发票日期之前最近的一个价格。

#3

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.

虽然你的问题的格式对我来说有点太乱了所以得到所有的细节,但看起来确实看起来你正在寻找标准的SQL构造ROW_NUMBER()OVER与PARTITION和ORDER_BY - 它在PostgreSql 8.4中并且一直在Oracle [和MS SQL Server以及DB2 ......]已经有一段时间了,这是选择“按组”排名“顶部”(或“顶部N”)并且具有某种顺序的最方便的方式SQL查询。查阅,请参阅此处了解PosgreSQL特定的文档。

#1

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

#2