CODEWITHSUNDEEP
sqlsql-serverrow
user3020327
user3020327

Reputation: 1

row by row in sql server

I have a table with 250 rows, here is the structure

week   |    number  |      Valued       |   removed    |    status
----- -------------------------------------------------------------
  1     |    68       |     68            |   272     |     68
  2     |    400      |     196          |    784      |   196

For the week 1, number is equal to status and valued, 

              'removed'= 'valued' * 3

for the week 2, 

Valued = [current week's number] - [previous week's "removed"] + [previous week's "status"]

and the same calculation for all following weeks.

I am in need of a solution for this row by row calculation, based on previous weeks value.

Upvotes: 0

Views: 94

Answers (2)

Saad Surya
Saad Surya

Reputation: 505

I supposed that you do not have "Valued" column in you table structure and it is a computed column for you select statement, then 

Declare @test Table([week] int, [number] int, [removed] int, [status] int)
Insert into @test Values
(1, 68, 272, 68),
(2, 400, 784, 196)

;with cteTest(rowNum, [week], number, removed, status, valued)
As
(
Select top 1 cast(2 as int), [week], number, removed, status, number 
from @test order by [week]
Union All
Select a.rowNum+1, a.week, a.number,  a.removed, a.status, a.number-b.removed+b.status
From (select rowNum = cast(ROW_NUMBER() over(order by [week]) as int) , [week], 
number, removed, status from @test) a
inner join cteTest b on a.rowNum = b.rowNum 
)
Select week, number, removed, status, valued from cteTest

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You can get the value with the following query

SELECT t1.number - t2.removed + t2.status as valued
FROM table1 t1
INNER JOIN table1 t2 ON t1.week = t2.week+1

If you need an update query to update the valued column, you can use:

UPDATE table1
SET table1.valued = table1.number - t2.removed + t2.status
FROM table1
INNER JOIN table1 t2 ON table1.week = t2.week+1

Upvotes: 0

Related Questions