0

Lets say we have a table and I want to create a column similar to the "Damage Taken".

If the name of the row above is the same as this column, subtract last rows damage with this row. Else, subtract Life from this row subtract with the Damage of this row.

Name    Damage  Life                    Damage Taken
Bill    97      100     ->  (100 - 97) =    3
Bill    93      100     ->  (97 - 93) =     4
Bill    71      100     ->  (93 - 71) =     22
Bill    54      100     ->  (71 - 54) =     17
Stacy   112     200     ->  (200 - 112) =   88
Stacy   109     200     ->  (112 - 109) =   3
Stacy   91      200     ->  (109 - 91) =    18
Stacy   81      200     ->  (91 - 81) =     10
Stacy   62      200     ->  (81 - 62) =     19

I'm relatively new to SQL and think in terms of C++ with the underlying logic of the column like so.

for(int i = 0; i<Name.size(); i++)
    if(Name[i] == Name[i-1]){
        damageTaken[i] = Damage[i-1] - Damage[i];
    }
    else{
        damageTaken[i] = Life[i] - Damage[i];
    }
Kevin Ong
  • 3
  • 2
  • Take a look at Gordon's answer here https://stackoverflow.com/questions/17971988/sql-server-cumulative-sum-by-group. I'd flag this question as duplicated but I run out of flags :p – hardkoded Jun 29 '17 at 23:16
  • Do you have an ID or timestamp or something that provides the order of the rows? – APH Jun 29 '17 at 23:38

1 Answers1

0

A hint for you. try looking for LAG() function in SQL.

LAG (Transact-SQL)

ViKiNG
  • 1,294
  • 2
  • 19
  • 26