Recursive CTE with partition by column

Question

Below are the table structure

drop table if exists #Transactions

create table #Transactions (TID int, amt int)

insert into #Transactions values(1, 100)
insert into #Transactions values(1, -50)
insert into #Transactions values(1, 100)
insert into #Transactions values(1, -100)
insert into #Transactions values(1, 200)


;WITH y AS 
(
  SELECT TID, amt, rn = ROW_NUMBER() OVER (ORDER BY TID)
    FROM #Transactions
), x AS
(
    SELECT TID, rn, amt, rt = amt
      FROM y
      WHERE rn = 1
    UNION ALL
    SELECT y.TID, y.rn, y.amt, x.rt + y.amt
      FROM x INNER JOIN y
      ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
  FROM x
  ORDER BY x.rn
  OPTION (MAXRECURSION 10000);

This is similar to question recursive cte with running balance

But I need to running balance for each TIds..suppose if I insert to following transaction of TId=2

insert into #Transactions values(2, 100)
insert into #Transactions values(2, -50)
insert into #Transactions values(2, 100)
insert into #Transactions values(2, -100)
insert into #Transactions values(2, 200)

I need to achieve same only in recursive CTE method without lots of modification.. Please suggest a solution

server 2017..but i need to achieve in cte only... i know window function — Ajt, Dec 09 '19 at 08:27

Squirrel · Accepted Answer · 2019-12-09T08:36:12.340

2

You need to handle TID in your ROW_NUMBER() window function and also CTE JOIN

;WITH y AS 
(
  SELECT TID, amt, rn = ROW_NUMBER() OVER (PARTITION BY TID   -- <= added here
                                               ORDER BY TID)
    FROM #Transactions
), x AS
(
    SELECT TID, rn, amt, rt = amt
      FROM y
      WHERE rn = 1
    UNION ALL
    SELECT y.TID, y.rn, y.amt, x.rt + y.amt
      FROM x INNER JOIN y
      ON y.rn = x.rn + 1
     AND y.TID = x.TID                -- <= added here
)
SELECT TID, amt, RunningTotal = rt
  FROM x
  ORDER BY x.rn
  OPTION (MAXRECURSION 10000);

Any compelling reason that you must use CTE instead of a simple SUM() with window function ?

edited Dec 09 '19 at 08:36

answered Dec 09 '19 at 08:30

Squirrel

23,507
4
34
32

Thanks for quick response..dont want to change existing prcedure – Ajt Dec 09 '19 at 08:40
1

You should go for solution with window function. It is much more efficient than `CTE`. Check out the query by @Venkataraman R – Squirrel Dec 09 '19 at 09:07

Venkataraman R · Answer 2 · 2019-12-09T08:39:38.960

You dont need recursive CTE. You can simply for a PARTITION BY based approach.

SELECT tid
      , AMT
      , SUM(amt) OVER(PARTITION BY tid ORDER BY tid 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                      ) AS RunningTotal
FROM #Transactions

update Sorry. Just now, went through comments. If you have to use CTE, go with answer by @Squirrel. If you are fine with window functions, you can use the above approach.

score 2 · Answer 3 · answered Dec 09 '19 at 08:35

Add Partition with TID

FIDDLE DEMO

;WITH y AS 
(
  SELECT TID, amt, rn = ROW_NUMBER() OVER (PARTITION BY TID ORDER BY TID)
    FROM #Transactions
), x AS
(
    SELECT TID, rn, amt, rt = amt
      FROM y
      WHERE rn = 1
    UNION ALL
    SELECT y.TID, y.rn, y.amt,x.rt + y.amt 
      FROM x INNER JOIN y
      ON y.rn = x.rn + 1 AND x.TID = y.TID
)
SELECT TID, amt, RunningTotal = rt
  FROM x
  ORDER BY x.TID, x.rn
  OPTION (MAXRECURSION 10000);

Recursive CTE with partition by column

3 Answers3