SQL list account managers with predecessor

Question

I have the following that I can't seem to figure out. I'm trying to get a list of account managers on an account, their start/end date and the new account manager that took over the account on a single row.

Example:

DECLARE @accountManagerListing TABLE 
                               (
                                    accountNumber INT,
                                    accountManager VARCHAR(8),
                                    accountManagerStartDate DATE,
                                    accountManagerEndDate DATE
                               )

INSERT INTO @accountManagerListing (accountNumber, accountManager, accountManagerStartDate, accountManagerEndDate)
VALUES  (1, 'asmith', '01/01/2001', '01/31/2001'),
        (1, 'bsmith', '02/01/2001', '03/01/2002'),
        (1, 'csmith', '03/02/2002', '03/10/2002'),
        (1, 'dsmith', '03/11/2002', '06/01/2017'),
        (1, 'esmith', '06/02/2017', '08/17/2018'),
        (2, 'fsmith', '02/11/2018', '06/01/2018'),
        (2, 'gsmith', '06/02/2018', null)

Expected results:

Account Number  Old Account Manager  New Account Manager  Start Date  End Date

1                                    asmith               01/01/2001   01/31/2001
1               asmith               bsmith               02/01/2001   03/01/2002
1               bsmith               csmith               03/02/2002   03/10/2002
1               csmith               dsmith               03/11/2002   06/01/2017
1               dsmith               esmith               06/02/2017   08/17/2018
2                                    fsmith               02/11/2018   06/01/2018
2               fsmith               gsmith               06/02/2018   NULL

Answer 1

Use lag() :

select a.*, 
      lag(accountManager) over (partition by accountnumber order by accountManagerStartDate) as OldAccountManager
from @accountManagerListing a;