-2

In SQL Server, I have table with 4 column

 artid    num     A           B
   46     1     417636000     0 
   47     1     15024000      0
  102     1     3418105650    0
  226     1     1160601286    0
  60     668    260000        0
  69     668    5500000       0

I want in result set create new column for some calculation

This column should have value like this:

artid       num         a                      b                      newColumnValue
----------- ----------- ---------------------- ---------------------- ----------------------
46          1           417636000              0                      a-b+previous newColumnValue

I write this query, but I can't get previous newColumnValue:

select *, (a- b+ lag(a- b, 1, a- b) over (order by num,artid)) as newColumnValue
 FROM MainTbl
  ORDER BY  num,artid

i get this result

artid       num         a                      b                      newColumnValue
----------- ----------- ---------------------- ---------------------- ----------------------
46          1           417636000              0                      417636000
47          1           15024000               0                      432660000
102         1           3418105650             0                      3433129650
226         1           1160601286             0                      4578706936
60          668         260000                 0                      1160861286
69          668         5500000                0                      5760000

i want get this result

artid       num         a                      b                      newColumnValue
----------- ----------- ---------------------- ---------------------- ----------------------
46          1           417636000              0                      417636000
47          1           15024000               0                      432660000
102         1           3418105650             0                      3850765650
226         1           1160601286             0                      5011366936
60          668         260000                 0                      5011626936
69          668         5500000                0                      5017126936
dotnetdeveloper
  • 1
  • 2
  • Possible duplicate of [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) – theMayer Dec 09 '17 at 12:59
  • I believe you want `SELECT value - lag(value) OVER (ORDER BY Id) FROM table` - I'm not sure why you have the CTE set up there. It's also worth noting that SQL is declarative, not procedural - so declaring scalar values in stored procedures does not necessarily have the effect that you think it would. Stick your calculation into the `lag` function. – theMayer Dec 09 '17 at 13:04
  • @theMayer,exactly i want SELECT value - lag(value) OVER (ORDER BY Id) FROM table, but in query i can't access to new fild.(previousnewColumnValue) – dotnetdeveloper Dec 09 '17 at 13:13
  • i want have this query SELECT value - lag(previousnewColumnValue) OVER (ORDER BY previousnewColumnValue) FROM table – dotnetdeveloper Dec 09 '17 at 13:13
  • I'm ignoring the fact that you'd like to store the value in a column, because it rarely makes sense to do so... if that's a requirement, then it is trivial to modify the code to do that. – theMayer Dec 09 '17 at 13:17
  • i update my question, can you help me with new update? – dotnetdeveloper Dec 09 '17 at 14:32

2 Answers2

0

Given the following example data, 

+----+---+---+
| Id | A | B |
+----+---+---+
| 1  | 2 | 3 |
+----+---+---+
| 2  | 3 | 4 |
+----+---+---+
| 3  | 4 | 5 |
+----+---+---+
| 4  | 5 | 6 |
+----+---+---+
| 5  | 6 | 7 |
+----+---+---+

the following short SQL statement produces the desired output:

select A - B + lag(A - B, 1, 0) over (order by id)
from test

+----+
| -1 |
+----+
| -2 |
+----+
| -2 |
+----+
| -2 |
+----+
| -2 |
+----+

Note that the Lag function takes three arguments: the first is the expression you would like evaluated for the "lagged" record, the second is the amount of the lag (defaults to 1), and the third is the value to return if the expression cannot be computed (e.g. if it is the first record).

theMayer
  • 15,456
  • 7
  • 58
  • 90
0

You want cumulative sums (well, the difference between them):

select a, b, sum(a - b) over (order by num, artid)
from mytbl;

Note: SQL tables represent unordered sets. You need a column to specify the ordering to define previous. If you really only have two columns, then I might assume the ordering is based on a, and the query would be:

select a, b, sum(a - b) over (order by a)
from mytbl;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @goron lonoff thank's for your answer , i update my question with new help,can you help me with updated question? – dotnetdeveloper Dec 09 '17 at 14:32
  • @dotnetdeveloper . . . You should ask new questions as *new* questions, not in comments and not by modifying questions that already have answers. – Gordon Linoff Dec 10 '17 at 19:21