SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1(id int, value int, rev varchar(2))
insert into t1(id,value,rev) values(1,1001,'01')
insert into t1(id,value,rev) values(1,1002,'02')
insert into t1(id,value,rev) values(1,1001,'03')
insert into t1(id,value,rev) values(1,1002,'03')
insert into t1(id,value,rev) values(2,1003,'02')
insert into t1(id,value,rev) values(2,1004,'03')
insert into t1(id,value,rev) values(3,1003,'01')
insert into t1(id,value,rev) values(3,1005,'02')
insert into t1(id,value,rev) values(3,1005,'03')
insert into t1(id,value,rev) values(4,1002,'01')
10 rows affected

select id, value, rev from 
(
select * , lead(value) over(partition by id order by rev) as val1,
lag(value) over(partition by id order by rev) as val2
from t1
)A 
where (value<>val2 or value<>val1)


id
value
rev


1
1001
01

1
1002
02

1
1001
03

1
1002
03

2
1003
02

2
1004
03

3
1003
01

3
1005
02


create table t2(dateval date, amount int)
insert into t2 values
('01-01-2019',10),
('01-01-2019',10),
('01-01-2019',10),
('01-01-2019',10),
('02-01-2019',5),
('02-01-2019',5),
('02-01-2019',5),
('02-01-2019',5),
('03-01-2019',20),
('03-01-2019',20)
10 rows affected

select dateval,
SUM(amt) OVER(ORDER BY dateval ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as amt
from
(
SELECT 
    dateval, 
    SUM(amount) amt
FROM t2  group by dateval
)A


dateval
amt


01/01/2019 00:00:00
40

01/02/2019 00:00:00
60

01/03/2019 00:00:00
100

dateval	amt
01/01/2019 00:00:00	40
01/02/2019 00:00:00	60
01/03/2019 00:00:00	100