获取上个月的数据和本月的数据

Question

我有一张表，大致像这样

Transaction   Date        Gain/Loss
15464         7/31/2018    $500
15464         6/30/2018   -$200
59872         7/31/2018    $1000
59872         6/30/2018    $2500

如何添加另一列来计算每个特定交易编号的当月损益与上月损益之间的差额？即

Transaction   Date        Gain/Loss    Change in Gain/Loss
15464         7/31/2018    $500         $700
15464         6/30/2018   -$200        -$200
59872         7/31/2018    $1000       -$1500
59872         6/30/2018    $2500        $2500

Answer 1

我认为您可以使用相关子查询：

select t.*,
       (select top (1)
        from t as t2
        where t2.transaction = t.transaction and t2.date < t.date
        order by t2.date desc
       ) as prev_gain_loss
from t;

计算差值只是算术运算。

Answer 2

使用您的表作为基础-并假设每个月在该月的最后一天记录一笔交易：

SELECT  T1.Transaction
        , T1.[Date]
        , T1.[Gain/Loss]
        , T2.[Date]
        , T1.[Gain/Loss]-T2.[Gain/Loss] AS [Change in Gain/Loss]
FROM    MyTable T1 LEFT JOIN MyTable T2 ON T1.Transaction = T2.Transaction AND
                                           T1.[Date] = DATESERIAL(YEAR(T2.[Date]), MONTH(T2.[Date])+2,0)

假设您有两个表-一个显示本月的数字，另一个显示上个月的数字。

要将这两个表连接在一起，您可以将Transaction编号视为两个表之间的公共属性。

这两个表是同一个表的事实与Access无关-只要Access通过将表名别名为T1和T2来知道哪个是哪个即可。

FROM MyTable T1 LEFT JOIN MyTable T2 ON T1.Transaction = T2.Transaction

但是，这会给出太多的记录。7月份的事务将在第二个表中连接到自身，并连接到前一个月。上一个月的事务也将加入到自身和当前的月事务中。这将导致每个事务有四条记录。

| Transaction | T1.Date    | Gain/Loss | T2.Date    | Change in Gain/Loss |
|-------------|------------|-----------|------------|---------------------|
| 15464       | 31/07/2018 | £500.00   | 30/06/2018 | £700.00             |
| 15464       | 31/07/2018 | £500.00   | 31/07/2018 | £0.00               |
| 15464       | 30/06/2018 | -£200.00  | 30/06/2018 | £0.00               |
| 15464       | 30/06/2018 | -£200.00  | 31/07/2018 | -£700.00            |
| 59872       | 31/07/2018 | £1,000.00 | 30/06/2018 | -£1,500.00          |
| 59872       | 31/07/2018 | £1,000.00 | 31/07/2018 | £0.00               |
| 59872       | 30/06/2018 | £2,500.00 | 30/06/2018 | £0.00               |
| 59872       | 30/06/2018 | £2,500.00 | 31/07/2018 | £1,500.00           |

要仅对上个月的数字执行计算，我们需要将当月与上个月合并。

T1.[Date] = DATESERIAL(YEAR(T2.[Date]), MONTH(T2.[Date])+2,0)

此连接中的DATESERIAL计算将日期添加两个月，然后返回第二个月的第零天，等于前一个月的最后一天。(希望这是有意义的)。

| Transaction | Date       | Gain/Loss | Change in Gain/Loss |
|-------------|------------|-----------|---------------------|
| 15464       | 31/07/2018 | £500.00   | £700.00             |
| 15464       | 30/06/2018 | -£200.00  |                     |
| 59872       | 31/07/2018 | £1,000.00 | -£1,500.00          |
| 59872       | 30/06/2018 | £2,500.00 |                     | 

如果您必须在起始月份有数字，那么您可以使用NZ将空值转换为0。

, T1.[Gain/Loss]-NZ(T2.[Gain/Loss],0) AS [Change in Gain/Loss]

| Transaction | Date       | Gain/Loss | Change in Gain/Loss |
|-------------|------------|-----------|---------------------|
| 15464       | 31/07/2018 | £500.00   | £700.00             |
| 15464       | 30/06/2018 | -£200.00  | -£200.00            |
| 59872       | 31/07/2018 | £1,000.00 | -£1,500.00          |
| 59872       | 30/06/2018 | £2,500.00 | £2,500.00           |