How to increment a variable under a certain condition

Asked

Viewed 266 times

1

I’m having a little problem with SQL. I need to increment a variable sel2 from a condition defined in sel1, for example:

linha | sel1 | sel2
1     |  1   |  1
2     | -1   |  -1
3     | -1   |  -2
4     |  1   |  1
5     |  1   |  2
6     |  1   |  3
7     | -1   |  -1

so on and so forth

I tried to solve through the following code:

SELECT t1.linha ,
       t1.sel1 ,
       SUM(t2.sel1) sel2
FROM #A AS t1
INNER JOIN #A AS t2 ON t1.linha>= t2.linha
GROUP BY t1.linha,
         t1.sel1
ORDER BY t1.linha ASC

However, the result increases cumulatively, thus:

linha | sel1 | sel2
1     |  1   |  1
2     | -1   |  -1
3     | -1   |  -2
4     |  1   |  2
5     |  1   |  3
6     |  1   |  4
7     | -1   |  -3

The fact is that I could not get the expected result. Would anyone have any idea for the solution.

thanks

sql-server

2 answers

0

Good afternoon, this can be done this way:

create table #tmp1 (Linha int, Sel1 int, Sel2 int)
go

insert into #tmp1
            select Linha = 1, Sel1 = 1, Sel2 = 0
    Union   select Linha = 2, Sel1 = -1, Sel2 = 0
    Union   select Linha = 3, Sel1 = -1, Sel2 = 0
    Union   select Linha = 4, Sel1 = 1, Sel2 = 0
    Union   select Linha = 5, Sel1 = 1, Sel2 = 0
    Union   select Linha = 6, Sel1 = 1, Sel2 = 0
    Union   select Linha = 7, Sel1 = -1, Sel2 = 0
go

select * from #tmp1

declare @sel2 int = 0
update  #tmp1
    set Sel2    =   @sel2
    ,   @sel2   =   @sel2 + Sel1

select * from #tmp1

0

Thank you for the reply It is interesting, however the problem persists the case is that the variable "sel1" is previously defined (it is she who defines the condition), if the following value is equal to the previous one, sum with the previous one, if different, would have to take that value as the beginning of the count, for example:

line | sel1 | sel2 1 | 1 | 1 2 | -1 | -1 3 | -1 | -2 4 | 1 | 1 5 | 1 | 2 6 | 1 | 3 7 | -1 | -1 . | . | . . | . | . . | . | . No value is set for "line" I ran the code you suggested, but it didn’t work

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.