Update table with recursive CTE and over not working

Transact-SQL
1

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

2

I didn't quite understand the logic you are trying to implement, but based on a literal reading of what you said ( the C_C column that when row 'C_B' = 'C' it takes the previous value )

with cte as
(
select C_B, C_C, 
	case 
		when c_A = sum(C_A) OVER (order by C_DT rows between unbounded preceding and 1 preceding)  then 
			sum(C_A) OVER (order by C_DT rows between unbounded preceding and 1 preceding)
		else 
			sum(C_A) OVER (order by C_DT)
		end
			as Total 

from @TEST
) 
update cte set C_C= Total ;
3

with cte as
(
select C_B, C_C, sum(C_A) OVER (order by C_DT) - (CASE WHEN C_B = 'C' THEN C_A ELSE 0 END) as Total
from @TEST

)
update cte
set C_C= Total ;

4

Thank you both.
All I want is to accumulate the value of column C_A except when C_B='C' and order by C_DT(date)
Sorry but the result that I get with your select is:

1
3
3
10
15

And I´m looking for:
1
3
3
7
12

The diference is that rows 'D' and 'E' should not take into account row 'C'
Perhaps CTE is not the way to do it.
Thanks for your help.

5 
SELECT *
FROM @Test t
CROSS APPLY (
	SELECT new_C_C = SUM(x.C_A)
	FROM @Test x
	WHERE x.C_DT <= t.C_DT
		AND x.C_B <> 'C'
	) c
6

Fantastic!
Many thanks khtan!!!