sql-server - SQL 服务器 : get weekly deposit changes using recursive CTE

Question

我有一张账户存款表。

+-----------+------------+-----------+
| DepositId |    Date    |  Amount   |
+-----------+------------+-----------+
|         1 | 2014-06-12 | 2342,00   |
|         2 | 2014-08-05 | 23423,00  |
|         3 | 2014-09-07 | 7745,00   |
|....................................|
|        12 | 2014-12-05 | 35435,00  |
|        13 | 2014-12-11 | 353453,00 |
|        14 | 2014-12-29 | 53453,00  |
+-----------+------------+-----------+

我希望看到每周的余额变化如下:

+------------+----------+
|     Date   |  Amount  |
+------------+----------+
| 2014-10-07 | 74754,00 |
| 2014-10-14 | 74754,00 |
| 2014-10-21 | 6353,00  |
| 2014-10-28 | 6353,00  |
| ........   | ......   |
| 2014-12-30 | 53453,00 |
+------------+----------+

要查看过去 3 个月(~ 13 周)的变化，我可以使用以下查询:

select CONVERT(date, DATEADD(WEEK, -13, GETDATE())) as Date, ad.Amount
from AccountDeposits as ad
inner join
    (select Max(Date) as Date
    from AccountDeposits
    where (Date < DATEADD(WEEK, -13, GETDATE())))
    as ad2 on (ad.Date = ad2.Date)

    union all

select CONVERT(date, DATEADD(WEEK, -12, GETDATE())) as Date, ad.Amount
from AccountDeposits as ad
inner join
    (select Max(Date) as Date
    from AccountDeposits
    where (Date < DATEADD(WEEK, -12, GETDATE())))
    as ad2 on (ad.Date = ad2.Date)

......................................................

select CONVERT(date, DATEADD(WEEK, -1, GETDATE())) as Date, ad.Amount
from AccountDeposits as ad
inner join
    (select Max(Date) as Date
    from AccountDeposits
    where (Date < DATEADD(WEEK, -1, GETDATE())))
    as ad2 on (ad.Date = ad2.Date)

我必须使用递归公用表表达式来执行此操作，但在 CTE 的递归部分我不能使用 MAX() 函数。我应该如何使用 CTE 将此查询系列写入 on query？

Answer 1

我可能误解了这个问题(如果有的话，我深表歉意)但是如果问题是“对于有存款的每一周，给出该周和该周最后一天的总存款之和”，那么 T - 下面的 SQL 将给出正确的结果。

with myCte1 as
(
    select *, datepart(week,d.[Date]) as wk, datepart(year,d.[Date]) as yr, 
        dateadd(dd, 7-(datepart(dw,d.[Date])), d.[Date]) as weekEndDate
    from dbo.AccountDeposits as d
),

myCte2 as
(
    select *, sum(m.Amount) over (partition by m.yr, m.wk) as totalWeeklyAmt
    from myCte1 as m
)

select distinct m.weekEndDate, m.totalWeeklyAmt
from myCte2 as m