根据生效日期获取正确的价格 [英] Get Correct Price based on Effectivity Date

问题描述

我在根据生效日期获取产品的正确价格"时遇到问题.

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

例如，我有 2 个表:

Example, I have 2 tables:

一个.交易"表 --> 包含订购的产品，以及
湾Item Master"表 --> 包含产品价格和这些价格的生效日期

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

在交易表内:

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

在Item_Master"表中:PRODUCT_PKG_CODE PRODUCT_PKG_ITEM PRODUCT_ITEM_PRICE EFFECTIVITY_DATE产品A 产品A-01 25 6/1/2009产品A 产品A-02 22 6/1/2009产品A 产品A-03 20 6/1/2009产品A 产品A-01 15 5/1/2009产品A 产品A-02 12 5/1/2009产品A 产品A-03 10 5/1/2009产品A 产品A-01 19 4/1/2009产品A 产品A-02 17 4/1/2009产品A 产品A-03 15 4/1/2009

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

在我的报告中，我需要显示发票和订单，以及有效的订单商品的价格在付款时(发票日期).我的查询看起来像这样(我的源数据库是 Oracle):

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):

...显示每件商品的 2 个价格.
我做了一些其他不同的查询样式但无济于事，所以我决定是时候寻求帮助了.:)
感谢你们中的任何人分享你的知识.--CJ--

...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--

附言对不起，我的帖子甚至看起来都不正确！:D

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

推荐答案

Analytics 是您的朋友.例如，您可以使用 FIRST_VALUE() 函数获取给定产品的所有 product_item_price，按 effectivity_date(降序)排序，然后选择第一个.您还需要一个 DISTINCT，以便每笔交易只返回一行.

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

这篇关于根据生效日期获取正确的价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！