sql - 构建查询时将日期范围与特定日期进行比较

Question

我已经为这个问题绞尽脑汁三天了，也许你可以帮我解决这个问题。

所以，我有 3 个表。

一个称为商品价格，其中包含某些商品在给定时间内的价格。

表格基本上是这样的:

ItemName | Startdate  | Enddate    | Price
-------------------------------------------
Watch    | 01/01/2012 | 04/08/2012 | 180€
Watch    | 05/08/2012 | 31/12/2012 | 150€
Watch    | 01/01/2013 | 31/12/2013 | 200€

另一个表名为 Sales，其中包含每个月售出的商品数量。

表格如下所示:

ItemName | SaleMonth  | Number
-----------------------------
Watch    | 01/01/2012 | 93
  .             .        .
  .             .        .
  .             .        .
Watch    | 01/05/2013 | 59
Watch    | 01/06/2013 | 74

最后一个称为汇率，每个月从欧元到英镑的汇率。

ExchangeMonth | Rate  |
-----------------------
 01/01/2013   | 1.225 | 
 01/02/2013   | 1.166 |

要计算一个月内我获得了多少，我使用以下公式:

Gain_month_i = (ItemPrice_month_i-1 * ItemsSold_month_i-1)/ExchangeRate_month_i

我想计算我在给定时间段内获得了多少。

但如果我选择的时间段在我的项目价格表中的不同时间段重叠(例如，如果我想查看我在 01/02/2012 年和 2014 年 1 月 1 日)

Answer 1

问题的实质是您的 [销售] 数字是按月汇总的，但商品的价格可能在月初和月底之间发生了变化。因此，如果您在 2012 年 8 月售出了 50 block watch ，您无法确定价格为 180 时售出的数量以及价格降至 150 时售出的数量。

在我看来，唯一合理的解决方案是根据一个月内各种价格生效的天数使用平均月度价格。您可以通过使用像这样的 VBA 函数来实现:

Option Compare Database
Option Explicit

Public Function AverageMonthlyPrice(ItemName As String, AnyDayInMonth As Date) As Variant
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim FirstDayOfMonth As Date, LastDayOfMonth As Date, NumDaysInMonth As Long
Dim IntervalStart As Date, IntervalEnd As Date
Dim WeightedAveragePrice As Variant

WeightedAveragePrice = Null
FirstDayOfMonth = DateSerial(year(AnyDayInMonth), Month(AnyDayInMonth), 1)
LastDayOfMonth = DateAdd("d", -1, DateSerial(year(AnyDayInMonth), Month(AnyDayInMonth) + 1, 1))
NumDaysInMonth = DateDiff("d", FirstDayOfMonth, LastDayOfMonth) + 1
Set qdf = CurrentDb.CreateQueryDef("", _
        "SELECT * FROM [Item Price] " & _
        "WHERE ItemName = [pItemName] " & _
            "AND Enddate >= [pFirstDay] AND Startdate <= [pLastDay]")
qdf!pItemName = ItemName
qdf!pFirstDay = FirstDayOfMonth
qdf!pLastDay = LastDayOfMonth
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Do While Not rst.EOF
    IntervalStart = IIf(rst!StartDate < FirstDayOfMonth, FirstDayOfMonth, rst!StartDate)
    IntervalEnd = IIf(rst!EndDate > LastDayOfMonth, LastDayOfMonth, rst!EndDate)
    WeightedAveragePrice = Nz(WeightedAveragePrice, 0) + rst!Price * (DateDiff("d", IntervalStart, IntervalEnd) + 1) / NumDaysInMonth
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
If Not IsNull(WeightedAveragePrice) Then
    WeightedAveragePrice = CCur(WeightedAveragePrice)
End If
AverageMonthlyPrice = WeightedAveragePrice
End Function

现在您可以在 Access 中运行一个查询，将 [Sales] 表中每一行的销售数量、平均价格和汇率汇总在一起:

SELECT
    Sales.ItemName,
    Sales.SaleMonth,
    Sales.Number,
    AverageMonthlyPrice(Sales.ItemName, Sales.SaleMonth) AS AveragePrice,
    [Exchange Rate].Rate AS ExchangeRate
FROM
    Sales
    INNER JOIN
    [Exchange Rate]
        ON [Exchange Rate].ExchangeMonth = Sales.SaleMonth

...返回类似的内容:

ItemName  SaleMonth   Number  AveragePrice  ExchangeRate
--------  ----------  ------  ------------  ------------
Watch     2012-01-01      93  180                  1.225
Watch     2012-08-01      50  153.871              1.166
Watch     2013-06-01      74  200                  1.234
Watch     2013-05-01      59  200                  1.123

如果将该查询保存在 Access 中，则可以在其他查​​询中使用它(就像使用表一样)来执行其余计算。