sql query adding previous value to next value

Question

I want to add two columns values of my table E.g:

 Id Distance    Duration    ETA_Distance    ETA_Duration
 1  0             0           20                60
 2  14           20           NULL              NULL
 3  12           10           NULL              NULL
 4  15           70           NULL              NULL

Considering the table above, I want a SQL query which give me the result like below:

 Id Distance    Duration    ETA_Distance    ETA_Duration
 1  0             0           20                60
 2  14           20           34                80
 3  12           10           46                90
 4  15           70           61                160

See [Calculating simple running totals in SQL Server](http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server) — PM 77-1, Aug 29 '14 at 05:32

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Mohan, see the below answer, It should help you. Replace the @table with your table. I used the temp table just to test the code.

Declare @tab table (Id int,Distance int,   Duration int,    ETA_Distance int,   ETA_Duration int)
Insert into @tab values 
(1,0 ,  0,20  ,60  ),
(2,14, 20,NULL,NULL),
(3,12, 10,NULL,NULL),
(4,15, 70,NULL,NULL)


Select  X.Id,X.Distance,X.Duration,
        Y.ETA_Distance,Y.ETA_Duration
From    @tab X
Join    (
Select  B.Id,
        Sum(A.Distance) ETA_Distance,
        Sum(A.Duration) ETA_Duration 
From    (Select Id,ISNULL(ETA_Distance,Distance) Distance,ISNULL(ETA_Duration,Duration) Duration From @tab) A,
        (Select Id,ISNULL(ETA_Distance,Distance) Distance,ISNULL(ETA_Duration,Duration) Duration  From @tab) B
Where   A.Id <= B.Id
Group   By B.Id) Y
On      X.Id = Y. Id

Result:

enter image description here

Sql may look lengthy, but the logic is simple.

+1 And here is the link to that sample [`SQL Fiddle`](http://www.sqlfiddle.com/#!3/b427d/11) — Vishal Suthar, Aug 29 '14 at 06:28

sql query adding previous value to next value

1 Answers1