SQL Server: Get weekly deposit changes using recursive CTE
I have a table with accounts.
+-----------+------------+-----------+
| 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 the 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 change over the last 3 months (~ 13 weeks), I can use these 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 need to do this with recursive generic table expressions, but in the recursive part of the CTE, I cannot use the function MAX()
. How am I supposed to write this series of queries to query using CTE?
source to share
This uses two CTEs, one of which sums up our original data and the other uses a recursive CTE to generate all weeks, which allows us to also show weeks when there were no deposits. It also uses two related subqueries to get the summed data from the first CTE.
I think this suits 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
source to share
I may have misinterpreted the question (apologies if I have), but if the question is "for every week that there were deposits, please indicate the sum of all deposits for this week and the last day of this week", then the T -SQL below would give 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
source to share
I solved it without CTE ... First create a table with startdate and enddate for 13 weeks starting with 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 the amount, which will be the sum of the amount whose dates are between the start date and the 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
source to share