目前我正与一个表,看起来是这样的:
Month | Transaction | amount
2021-07-01| Annual Membership Fee| 45
2021-08-01| Annual Membership Fee| 145
2021-09-01| Annual Membership Fee| 2940
2021-10-01| Annual Membership Fee| 1545
的 amount
该表上的每月总量(前。 我有100个客户支付15美元的年度成员资格,所以我每月总额将$1500).
但是我想做的(而我不知道如何)是分的数额12和传播它的未来,以便有一个每月收入的每个月。 作为一个例子2021-09-01我会得到如下:
$2490/12 = $207.5 (dollars per month for the next 12 months)
in 2021-09-01 I would only get $207.5 for that specific month.
On 2021-10-01 I would get $1545/12 = $128.75 plus $207.5 from the previous month (total = $336.25 for 2021-10-01)
And the same operation would repeat onwards. The last period that I would collect my $207.5 from 2021-09-01 would be in 2022-08-01.
我想知道如果有人能给我一个想法是如何执行这一SQL query/CTE?