获取上个月的数据以及当月的数据 [英] Get previous month's data alongside current month

问题描述

我有一张大致像这样的桌子

I have a table that roughly looks like this

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

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

How can i add another column that calculates the difference between the current month's gain/loss and the previous month's for each specific transaction number? i.e.

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

推荐答案

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

I think you can use a correlated subquery:

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;

计算差值只是算术运算.

Calculating the difference is just arithmetic.

这篇关于获取上个月的数据以及当月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！