SQL Server: get weekly deposit changes using recursive CTE

Question

I have a table with account deposits.

+-----------+------------+-----------+
| 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  |
+-----------+------------+-----------+

I want to see weekly balance change like this:

+------------+----------+
|     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 |
+------------+----------+

To see this changes for past 3 month (~ 13 weeks) I can use this queries:


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)

I have to do this with recursive Common Table Expressions but in recursive part of CTE I can't use MAX() function. How I should write this query series to on query with CTE?

Is there an account ID or anything on the table that groups the data together or is each table unique to a single account? http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver is an example on how to do this. — xQbert, Jan 06 '15 at 15:24
@xQbert It's deposit changing id, table unique to a single account — biglazypanda, Jan 06 '15 at 17:19
Then I believe the example using an analytic over partition by is what you're after. — xQbert, Jan 06 '15 at 17:42
Ideally your expected output should relate to the inputs you have given us. (just for next time) — G B, Jan 06 '15 at 22:26
@GB described input and output made by queries form the same data so, they correspond to each other — biglazypanda, Jan 07 '15 at 12:00
Its all good, no biggie, but what I meant was that although your overall input and outputs may relate to each other the output sample doesn't relate to the sample input you gave here... e.g. there is a row for 2014-10-07 in the output but no input data for that week at all. So you could give us all the data for October and then summarise that same data. But really - its no big deal other than the fact that the better your questions are, the more attention they will get. — G B, Jan 07 '15 at 18:41

score 2 · Answer 1 · answered Jan 06 '15 at 20:03

I may have misinterpreted the question (apologies if I have) but if the question is "for each week when there were deposits, give the sum of the total deposits for that week and the last day of that week" then the T-SQL below would give the correct results.

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

I need result with balance amount at the end of every week, It's good solution, but I need some more — biglazypanda, Jan 07 '15 at 12:03

score 1 · Accepted Answer · answered Jan 06 '15 at 22:34

This uses two CTE's, one summarises our source data and the other uses a recursive CTE to generate all the weeks, this allows us to also show weeks where there were no deposits. It also uses two co-related subqueries to get the sumarised data from the first CTE.

I think this satisfies the requirements of your assignment.'


--NOTE: this gets data based on week end date, so all deposits for week of @WeeksHistory ago not just the deposits after the date (today  minus @WeeksHistory weeks).
--NOTE: this gets all historical data so that we can start with opening balance of $0 otherwise Closing balance wont take previous deposits into account.
--NOTE: this gets the week starting @WeeksHistory ago and also this week so you will end up with @WeeksHistory +1 records - you might want to adjust this as necessary

-- set up our source data
declare @AccountDeposits table (DepID int, AcctHolderID int, TxnDate date, Amount numeric(10,2)) 

insert into @AccountDeposits 
values
    (1, 3,'12-25-2014', 2423.00),
    (2, 1,'12-13-2014',4231.00),
    (3, 2,'11-01-2014',666.00),
    (4, 1,'11-01-2014',4241.34),
    (5, 4,'10-23-2014',4221.00),
    (6, 2,'10-22-2014',9992.00),
    (7, 2,'10-04-2014',3524.00),
    (8, 2,'10-14-2014',3524.00),
    (9, 2,'10-15-2014',3524.00),
    (10, 2,'10-16-2014',3524.00),
    (11, 3,'10-14-2014',3524.00),
    (12, 3,'10-15-2014',3524.00),
    (13, 3,'10-16-2014',3524.00),
    (14, 1,'10-01-2014',3524.00),
    (15, 2,'10-01-2014',3524.00),
    (16, 3,'10-01-2014',3524.00),
    (17, 4,'01-01-2015',3524.00)

declare @AcctHolderID as int = 2
declare @WeeksHistory int = -13

select dateadd(week,@WeeksHistory,getdate()) ThirteenWeeksAgo

;with 
    src (AcctHolderID, WeekEndsOn, Amount)
as (select
        AcctHolderID, 
        DATEADD(DAY, 7-DATEPART(WEEKDAY, TxnDate), TxnDate),
        SUM(Amount)
    from @AccountDeposits
    where AcctHolderID = @AcctHolderID -- we filter up here so that we arent processing data we dont care about.
    group by
        AcctHolderID, 
        DATEADD(DAY, 7-DATEPART(WEEKDAY, TxnDate), TxnDate)
    ),
    r_cte (AcctHolderID, WeekEndsOn, TotalDep, ClosingBal)
as (select
        AcctHolderID,
        dateadd(ww,-1,Min(WeekEndsOn)),
        convert(numeric(10,2),0.00),
        convert(numeric(10,2),0.00)
    from
        src
    group by 
        AcctHolderID
    union all
    select
        r_cte.AcctHolderID,
        dateadd(WW,1,r_cte.WeekEndsOn),
        convert(numeric(10,2),ISNULL((select Amount from src where AcctHolderID = r_cte.AcctHolderID and WeekEndsOn = dateadd(WW,1,r_cte.WeekEndsOn)),0)),
        convert(numeric(10,2),ISNULL((select Amount from src where AcctHolderID = r_cte.AcctHolderID and WeekEndsOn = dateadd(WW,1,r_cte.WeekEndsOn)),0) + r_cte.ClosingBal)
    from 
        r_cte
    where
        AcctHolderID = r_cte.AcctHolderID 
        and r_cte.WeekEndsOn < DATEADD(DAY, 7-DATEPART(WEEKDAY, Getdate()), DATEADD(WW,-1,Getdate()))
    )
select AcctHolderID, DATEDIFF(ww, WeekEndsOn, getdate()) as WeeksAgo, WeekEndsOn, TotalDep, ClosingBal 
from r_cte
where r_cte.WeekEndsOn > dateadd(week,@WeeksHistory,getdate())
order by
    AcctHolderID,
    WeekEndsOn

AcctHolderId is redunant, cause table contains deposits only for one account, but at all this solution is right — biglazypanda, Jan 07 '15 at 12:09

score 0 · Answer 3 · answered Jan 06 '15 at 20:05

I have solved it without CTE... First create table with startdate and enddate for 13 weeks starting from getdate ().

Create table weeklydates
(Startdate date,
 Enddate date
)
Declare @startdate date
Declare @enddate date

Set @startdate = cast (dateadd (week,-13,getdate ()) as date)
Set @enddate = dateadd (day,7,@startdate)

While @enddate < = getdate ()
Begin
Insert into weeklydates
Select @startdate, @enddate
Set @startdate = dateadd (day,1,@enddate)
Set @enddate = dateadd (day,7,@startdate)
End

Now use this table to display amount which will be sum of amount whose dates fall between start date and end date

Select a.startdate,a.enddate, (select sum (amount) from yourtablehavingamount as b 
Where b.deposit >=a.startdate and b.deposit <=a.enddate)
From weeklydates as a

score -1 · Answer 4 · answered Jan 06 '15 at 15:25

-1

USE:

SELECT TOP 1 [Date] FROM AccountDeposits --Add WHERE Clause ORDER BY [Date] DESC

:) David

answered Jan 06 '15 at 15:25

David P

2,027
3
15
27

The TOP or OFFSET operator is not allowed in the recursive part of a recursive common table expression – biglazypanda Jan 06 '15 at 17:35

SQL Server: get weekly deposit changes using recursive CTE

4 Answers4