Access SQL-在每个记录中仅包含开始的日期范围内进行分配

debugcn 发表于 Dev

mvbentes

我正在使用Access 2010

我有两个表：

价格表，其中列出了该价格生效的时间。价格更新后，我会得到另一个包含新价格及其生效时间的记录。没有键，该表由两个字段（合同，产品代码）查询。
订单，我在其中订购了订购日期和价格。

我想做的是在价格表中检查下订单时哪个价格处于活动状态。我试图从Order PLUS中获取一个包含所有字段的表，并从表ContracPrices中获取适当的ContractedPrice和PriceValidFrom。

示例：示例解决的数据库（具有@carexcer贡献）

Orders
OrderNumber Contract    ProductCode OrderDate   PurchasePrice
Ord1    Cont1   AAA 07/05/2012  19,23
Ord1    Cont1   BBB 07/05/2012  5,66
Ord1    Cont1   CCC 07/05/2012  6,64

ContractPrices
Contract    ProductCode ContractedPrice PriceValidFrom
Cont1   AAA 21,14   11/02/2010 18:03:01
Cont1   AAA 21,84   08/10/2010 12:58:55
Cont1   AAA 22,06   22/06/2011 22:50:42
Cont1   AAA 1923,7  27/09/2011 14:17:08
Cont1   AAA 20      03/07/2012 12:40:36
Cont1   AAA 22,53   01/08/2012 21:37:26
Cont1   AAA 22,53   07/08/2012 19:33:29
Cont1   AAA 22,53   03/09/2012 18:26:38
Cont1   AAA 22,53   04/09/2012 16:20:09
Cont1   AAA 22,53   05/11/2012 18:53:18
Cont1   AAA 20      11/04/2013 15:28:49
Cont1   AAA 19,84   08/09/2013 19:00:10

DesiredResult
OrderNumber Contract    ProductCode OrderDate   PurchasePrice   ContractedPrice PriceValidFrom
Ord1    Cont1   AAA 07/05/2012  19,23   19,23   27/09/2011 14:17:08

请注意，我不限于从单个合同订购产品代码。与ProductCode的合同很多。

我将对VBA开放。我只是不知道如何在不离开Access环境的情况下解决问题。我是数据库新手。

我经过了蛮力测试，发现我们购买的商品的价格超出了合同规定的价格，因此想测试整个数据库。

我尝试搜索此问题，但无法简洁表达问题。

Carexcer

我认为此查询将解决您的问题：

SELECT
    t1.OrderNo, 
    t1.Contract,
    t1.ProductCode,
    MAX(t2.PriceValidityStartDate) as PriceValidityStartDate,
    MAX(t2.ContractPrice) AS PriceAtTime
FROM 
ORDERS t1 
LEFT OUTER JOIN 
ContractPrices t2
ON t1.Contract = t2.Contract AND t1.ProductCode = t2.ProductCode 
WHERE 
    t1.OrderDate > t2.PriceValidityStartDate
GROUP BY 
    t1.OrderNo, 
    t1.Contract,
    t1.ProductCode