Hi,

The select below works well to accumulate the C_A column ordered by date.

However, I would like to accumulate so long as C_B<>'C'

This is the definition:

DECLARE @Test TABLE

(

C_A INT,

C_B CHAR(1),

C_DT DATE,

C_C INT

)

INSERT INTO @Test

VALUES (1,'A','20150101',0),

(2,'B','20150102',0),

(3,'C','20150103',0),

(4,'D','20150104',0),

(5,'E','20150105',0);

with cte as

(

select C_B, C_C, sum(C_A) OVER (order by C_DT) as Total

from @TEST

)

update cte set C_C= Total ;

And this is the actual result:

C_A C_B C_DT C_C

1 A 2015-01-01 1

2 B 2015-01-02 3

3 C 2015-01-03 6

4 D 2015-01-04 10

5 E 2015-01-05 15

And this is how I would like it to be (notice the C_C column that when row 'C_B' = 'C' it takes the previous value.

C_A C_B C_DT C_C

1 A 2015-01-01 1

2 B 2015-01-02 3

3 C 2015-01-03 3

4 D 2015-01-04 7

5 E 2015-01-05 12

I tried to include a where clause at the end but it does not work. Can anyone help?

many thanks,

Pepe