CODEWITHSUNDEEP
sql-server
Lajith
Lajith

Reputation: 1867

Recursive CTE with partition by column

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)

Required output

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

Upvotes: 0

Views: 1022

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12959

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.

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

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 ?

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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);

Upvotes: 2

Related Questions