SQL Server 2014

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

create table test (ACCOUNT_NO int, [Date] DateTime, Amount int)
insert into test values
(1, '2014-03-23 00:00:00.000', 100),
(1, '2014-03-24 00:00:00.000', 200),
(1, '2014-03-25 00:00:00.000', 50),
(2, '2014-03-23 00:00:00.000', 1111),
(3, '2014-03-24 00:00:00.000', 1200),
(3, '2014-03-25 00:00:00.000', 1300);

6 rows affected

SELECT ACCOUNT_NO,
       [Date],
       Amount,
       LAG(Amount, 1, 0) OVER(PARTITION BY ACCOUNT_NO ORDER BY [Date]) AS "Yesterday Amount"
FROM test


ACCOUNT_NO
Date
Amount
Yesterday Amount


1
2014-03-23 00:00:00.000
100
0

1
2014-03-24 00:00:00.000
200
100

1
2014-03-25 00:00:00.000
50
200

2
2014-03-23 00:00:00.000
1111
0

3
2014-03-24 00:00:00.000
1200
0

3
2014-03-25 00:00:00.000
1300
1200

ACCOUNT_NO	Date	Amount	Yesterday Amount
1	2014-03-23 00:00:00.000	100	0
1	2014-03-24 00:00:00.000	200	100
1	2014-03-25 00:00:00.000	50	200
2	2014-03-23 00:00:00.000	1111	0
3	2014-03-24 00:00:00.000	1200	0
3	2014-03-25 00:00:00.000	1300	1200