CODEWITHSUNDEEP
sql
David Chen
David Chen

Reputation: 59

SQL: Using previous column for current column calculation

Let say I have a table like this

Id | Name | Length | Distance
1  | AB1  | 100    | 3600
2  | AB2  | 50     | 
2  | AB3  | 100    | 
3  | AB4  | 50     | 
4  | AB5  | 100    | 
5  | AB6  | 50     |

But I want a sql code to update to this (Takes previous distance and add the length)

Id | Name | Length | Distance
1  | AB1  | 100    | 3600
2  | AB2  | 50     | 3650
3  | AB3  | 100    | 3750
4  | AB4  | 50     | 3800
5  | AB5  | 100    | 3900
6  | AB6  | 50     | 3950

I'm thinking something like

UPDATE Table
SET Distance = (SELECT Distinct FROM table WHERE id=id-1)+Length
etc...

Upvotes: 0

Views: 73

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

If you have window functions

Declare @Table table (ID int,Name varchar(25),Length int,Distance int)
Insert into @Table values
(1,'AB1',100,3600),
(2,'AB2',50,0 ),
(3,'AB3',100,0),
(4,'AB4',50,0),
(5,'AB5',100,0),
(6,'AB6',50,0)

Select ID,Name,Length
      ,Distance = sum(IIF(Distance>0,Distance,Length)) over (Order by ID)
    From @Table

Returns

ID  Name    Length  Distance
1   AB1     100     3600
2   AB2     50      3650
3   AB3     100     3750
4   AB4     50      3800
5   AB5     100     3900
6   AB6     50      3950

This is a 2008 version -- Returns the same as above

Declare @Table table (ID int,Name varchar(25),Length int,Distance int)
Insert into @Table values
(1,'AB1',100,3600),
(2,'AB2',50,0 ),
(3,'AB3',100,0),
(4,'AB4',50,0),
(5,'AB5',100,0),
(6,'AB6',50,0)

Select A.ID
      ,A.Name
      ,A.Length
      ,Distance = Sum(case when B.Distance>0 then B.Distance else B.Length end)
 From  @Table A
 Join  @Table B on (B.ID<=A.ID)
 Group By A.ID
      ,A.Name
      ,A.Length
 Order By 1

If you are interested, I also have

Select [dbo].[udf-Date-Elapsed](@Date1,@Date2)

Returns

002:16:00:00   -- Or 2 Day 16 Hours 0 Minutes

Upvotes: 1

Wil Wang
Wil Wang

Reputation: 84

Using common table expression and assuming your IDs are sequential (otherwise use a ROW_NUMBER() function to ensure the sequential IDs:

DECLARE @mytable AS TABLE (
    Id INT,
    Name VARCHAR(10),
    Length INT,
    Distance INT
)
INSERT INTO @myTable (id, Name, Length, Distance)
VALUES (1, 'AB1', 100, 3600),
(2, 'AB2', 50, NULL),
(3, 'AB3', 100, NULL),
(4, 'AB4', 50, NULL)

SELECT * FROM @myTable;

; WITH cte AS (
    SELECT TOP 1 Id, Name, Length, Distance FROM @myTable
    UNION ALL
    SELECT mt.Id, mt.Name, mt.Length, cte.Distance + ISNULL(mt.Length, 0)
    FROM cte
    INNER JOIN @myTable mt ON mt.Id-1 = cte.Id
)

SELECT * FROM cte;

Upvotes: 0

Related Questions