SQL Server 2022

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

SELECT *
INTO dbo.Employee
FROM(VALUES(1,'Ashley'),
           (2,'Sam'),
           (3,'Samantha'),
           (4,'Jessica'),
           (5,'Tom'))V(EmployeeID,Name);

SELECT *
INTO dbo.Salary
FROM (VALUES(1,1,100),
            (2,1,50),
            (3,1,100),
            (4,1,50),
            (1,2,100),
            (2,2,50),
            (5,2,100),
            (2,3,20),
            (3,3,100),
            (4,3,50),
            (5,3,100))V(EmployeeID,Week,Salary);
16 rows affected

WITH EmployeeWeeks AS(
    SELECT DISTINCT
           E.EmployeeID,
           S.Week
    FROM dbo.Employee E
         CROSS JOIN dbo.Salary S)
SELECT EW.EmployeeID,
       S.Week,
       S.Salary,
       LEAD(S.Week) OVER (PARTITION BY EW.EmployeeID ORDER BY EW.Week) AS NextWeek,
       LEAD(S.Salary) OVER (PARTITION BY EW.EmployeeID ORDER BY EW.Week) AS NextSalary
FROM EmployeeWeeks EW
     LEFT JOIN dbo.Salary S ON EW.EmployeeID = S.EmployeeID
                           AND EW.Week = S.Week
ORDER BY EW.[Week],
         EW.EmployeeID;


EmployeeID
Week
Salary
NextWeek
NextSalary


1
1
100
2
100

2
1
50
2
50

3
1
100
null
null

4
1
50
null
null

5
null
null
2
100

1
2
100
null
null

2
2
50
3
20

3
null
null
3
100

4
null
null
3
50

5
2
100
3
100

1
null
null
null
null

2
3
20
null
null

3
3
100
null
null

4
3
50
null
null

5
3
100
null
null


DROP TABLE dbo.Employee;
DROP TABLE dbo.Salary;

EmployeeID	Week	Salary	NextWeek	NextSalary
1	1	100	2	100
2	1	50	2	50
3	1	100	null	null
4	1	50	null	null
5	null	null	2	100
1	2	100	null	null
2	2	50	3	20
3	null	null	3	100
4	null	null	3	50
5	2	100	3	100
1	null	null	null	null
2	3	20	null	null
3	3	100	null	null
4	3	50	null	null
5	3	100	null	null