SQL Server 2022

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

create table mytable (
  Contract int,	
  DateValue	date,
  Amount int	
);

insert into mytable values
(111,	'20-June-2023',	100),
(111,	'21-June-2023',	150),
(111,	'21-June-2023',	250),
(111,	'21-June-2023',	200),
(222,	'20-June-2023',	300),	
(222,	'21-June-2023',	350),
(222,	'22-June-2023',	450),
(333,	'23-June-2023',	100),
(333,	'24-June-2023',	200),
(333,	'24-June-2023',	250);
10 rows affected

select *, lag(Amount) over (partition by Contract order by DateValue) as prev_value
from mytable


Contract
DateValue
Amount
prev_value


111
2023-06-20
100
null

111
2023-06-21
150
100

111
2023-06-21
250
150

111
2023-06-21
200
250

222
2023-06-20
300
null

222
2023-06-21
350
300

222
2023-06-22
450
350

333
2023-06-23
100
null

333
2023-06-24
200
100

333
2023-06-24
250
200


select *, Amount - lag(Amount) over (partition by Contract order by DateValue) as [Calculated Column]
from mytable


Contract
DateValue
Amount
Calculated Column


111
2023-06-20
100
null

111
2023-06-21
150
50

111
2023-06-21
250
100

111
2023-06-21
200
-50

222
2023-06-20
300
null

222
2023-06-21
350
50

222
2023-06-22
450
100

333
2023-06-23
100
null

333
2023-06-24
200
100

333
2023-06-24
250
50

Contract	DateValue	Amount	prev_value
111	2023-06-20	100	null
111	2023-06-21	150	100
111	2023-06-21	250	150
111	2023-06-21	200	250
222	2023-06-20	300	null
222	2023-06-21	350	300
222	2023-06-22	450	350
333	2023-06-23	100	null
333	2023-06-24	200	100
333	2023-06-24	250	200