SQL Server solution to simple recursive function

Question

I am looking for a SQL Server solution for a simple recursive formula. In the example, X is my column of numbers and Y is the column I am trying to create with a SQL Query.

I have a list of numbers, denoted X, and wish to produce a special kind of running sum that is not allowed to go less than 0, denoted Y.

Base Case

Y₁ = MAX(X₁,0)

Recursive Rule

Y_i = MAX(X_i+Y_i-1,0)

EXAMPLE:

id     X(Input)    Y(Output)
1      15          15
2      -87         0
3      26          26
4      -87         0
5      4           4
6      -19         0
7      34          34
8      -4          30
9      40          70
10     -14         56

Do you have a column that specifies the ordering of the rows? SQL tables are inherently unordered, so if this is your entire table, you cannot do what you want. — Gordon Linoff
– Gordon Linoff, Commented Mar 18, 2014 at 0:52

Gordon Linoff · Accepted Answer · 2014-03-18 00:55:56Z

1

Assuming you have an id column that specifies the ordering, I am pretty sure you have to do this with a recursive CTE. The problem is that the "set negative numbers to zero" complicates the situation.

Let me assume that the id identifies the ordering.

with t as (
      select t.*, row_number() over (order by id) as seqnum
      from table t
     ),
     cte as (
      select X,
             (case when X < 0 then 0 else X end) as Y
      from t
      where id = 1
      union all
      select tnext.X,
             (case when tnext.X + cte.Y < 0 then 0 else tnext.X + cte.Y end) as Y
      from cte join
           t tnext
           on t.id + 1 = tnext.id
     )
select *
from cte;

answered Mar 18, 2014 at 0:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3431083 Over a year ago

I successfully implemented this type of solution, but was wondering if it is flexible enough to handle the extension of the problem wherein I add a varchar column "product" and need to keep this same special running positive sum unique to each "product". When ordered by id, the products would occur multiple times and appear randomly in the list.

Community · Accepted Answer · 2017-05-23 12:11:10Z

Using a cursor and a table variable to catch the calculated values might be good for performance.

declare @T table
(
  id int,
  X int,
  Y int
);

declare @id int;
declare @X int;
declare @Y int;

set @Y = 0;

declare C cursor local static forward_only read_only for
  select T.id, T.X
  from T
  order by T.id;

open C;

fetch next from C into @id, @X;
while @@fetch_status = 0
begin
  set @Y = case when @X + @Y < 0 then 0 else @X + @Y end;
  insert into @T(id, X, Y) values (@id, @X, @Y);
  fetch next from C into @id, @X;
end

close C;
deallocate C;

select T.id, T.X, T.Y
from @T as T
order by T.id;

SQL Fiddle

Have a look at Best approaches for running totals by Aaron Bertrand

Collectives™ on Stack Overflow

SQL Server solution to simple recursive function

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related