How can i get the data as below using sql

Transact-SQL
1

Hi
Can you help me in getting data as below

Capture

I need to get the data substracted by row and want to use that figure in the above row

EG: third row ---> 21.00 - 3.00 = 18.00
2nd row ----> 18.00 - 2.00 = 16.00
3rd row -----> 16.00 - 1.00 = 15.00
and so on

sample data

				SELECT * FROM  (SELECT '1' AS SN,'1.00' AS DUR ,'16.00' AS COL1, '15.00' AS COL2 UNION ALL
				SELECT '2' AS SN ,'2.00' AS DUR,'18.00' AS COL1, '16.00' AS COL2 UNION ALL
				SELECT '3' AS SN,'3.00' AS DUR ,'21.00' AS COL1, '18.00' AS COL2 ) A
2

Select sn , dur , col1 , col1 - dur as col2
From table

3

Thank You very much for your reply...

How can i get the col1 to get updated from col2
eg: third row col2 ---18.00 to be updated to 2nd row col1 --- 18.00 and so on

4

hi there is .. LAG ...

SAMPLE DATA 
drop table sample_data
go 

create table sample_data 
(
rn int , 
dur float , 
col1 float 
)
GO 

insert into sample_data select 1,1.00,16.00
insert into sample_data select 2,2.00,18.00
insert into sample_data select 3,3.00,21.00
GO 

SELECT * FROM sample_data
GO

SELECT 
     RN
   , DUR
   , COL1
   , LAG(COL1) OVER( ORDER BY RN ) 
FROM 
   sample_data

image

5

Hi seems like there is some miscommunication....
for the table below

drop table sample_data
go
create table sample_data
(
rn int ,
dur float
)
GO

insert into sample_data select 1,1.00
insert into sample_data select 2,2.00
insert into sample_data select 3,3.00
GO

SELECT * FROM sample_data
GO

i need a new column with the last row should be 21 (Fixed number for the last row) - 3 (dur) = 18

2nd row last col shloud be 18 (result of 3rd row) - 2 (dur) = 16
3rd row last col shloud be 16 (result of 2nd row) - 1 (dur) = 15

|rn|dur|calculated_col|
|1 |1 |15|
|2 |2 |16|
|3 |3 |18|

Thanking you in advance

6

hi hope this helps

i have done it using recursive cte

;WITH rec_cte
AS (
    SELECT top 1 
        rn , dur , col1-dur as ok  from sample_data  order by rn desc 
    UNION ALL
    SELECT    
        b.rn , b.dur , a.ok-b.dur
    FROM    
        rec_cte  a join    sample_data b on a.rn-1 = b.rn
)
SELECT 
    *
FROM 
    rec_cte

image

7 
;WITH rec_cte
AS (
    SELECT top 1 
        rn , dur , col1-dur as ok  from sample_data  order by rn desc 
    UNION ALL
    SELECT    
        b.rn , b.dur , a.ok-b.dur
    FROM    
        rec_cte  a join    sample_data b on a.rn-1 = b.rn
)
SELECT ' Rec CTE SQL ', 
    *
FROM 
    rec_cte 
ORDER BY 
    rn

image