SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Participants (
  ParticipantID INT,
  ParticipantLastName VARCHAR(20),
  ParticipantFirstName VARCHAR(20)
);
INSERT INTO Participants VALUES (1, 'smith', 'john');
INSERT INTO Participants VALUES (2, 'jackson', 'andrew');
INSERT INTO Participants VALUES (3, 'mayfield', 'baker');

Create Table Programs (
  ProgramID INT,
  ProgramTitle VARCHAR(255),
  EngagePoints INT,
  ProgramDate DATE
);
INSERT INTO Programs VALUES (100, 'program1', 2, '2019-10-21');
INSERT INTO Programs VALUES (200, 'program2', 3, '2020-1-2');
INSERT INTO Programs VALUES (300, 'program3', 1, '2020-2-6');

Create Table ParticipantProgramDetails (
  ParticipantID INT,
  ProgramID INT,
  ProgramDate DATE
);
INSERT INTO ParticipantProgramDetails VALUES (1, 100, '2019-10-21');
INSERT INTO ParticipantProgramDetails VALUES (2, 100, '2019-10-21');
INSERT INTO ParticipantProgramDetails VALUES (2, 200, '2020-1-2');
INSERT INTO ParticipantProgramDetails VALUES (3, 200, '2020-1-2');
INSERT INTO ParticipantProgramDetails VALUES (3, 300, '2020-2-6');
11 rows affected

SELECT p.ParticipantID, p.ParticipantFirstName, p.ParticipantLastName, 
SUM(pr.EngagePoints) As '2019EngagePoints'
INTO #tempTable2019
FROM Participants p
FULL OUTER JOIN ParticipantProgramDetails ppd
    on p.ParticipantID = ppd.ParticipantID
FULL OUTER JOIN Programs pr
    on ppd.ProgramID = pr.ProgramID
WHERE ppd.ProgramDate < '2020-1-1'
GROUP BY p.ParticipantID, p.ParticipantFirstName, p.ParticipantLastName

/*SELECT * FROM #tempTable2019*/
2 rows affected

SELECT p.ParticipantID, p.ParticipantFirstName, p.ParticipantLastName, 
SUM(pr.EngagePoints) As '2020EngagePoints'
INTO #tempTable2020
FROM Participants p
FULL OUTER JOIN ParticipantProgramDetails ppd
    on p.ParticipantID = ppd.ParticipantID
FULL OUTER JOIN Programs pr
    on ppd.ProgramID = pr.ProgramID
WHERE ppd.ProgramDate >= '2020-1-1'
GROUP BY p.ParticipantID, p.ParticipantFirstName, p.ParticipantLastName

/*SELECT * FROM #tempTable2020*/
2 rows affected

SELECT p.ParticipantID, p.ParticipantFirstName, p.ParticipantLastName,
ISNULL(SUM(tt1.[2019EngagePoints]), 0) As '2019 Engage Points',
ISNULL(SUM(tt2.[2020EngagePoints]), 0) As '2020 Engage Points'
FROM Participants p
FULL OUTER JOIN #tempTable2019 tt1
    on p.ParticipantID = tt1.ParticipantID
FULL OUTER JOIN #tempTable2020 tt2
    on p.ParticipantID = tt2.ParticipantID
GROUP BY p.ParticipantID, p.ParticipantFirstName, p.ParticipantLastName


ParticipantID
ParticipantFirstName
ParticipantLastName
2019 Engage Points
2020 Engage Points


1
john
smith
2
0

2
andrew
jackson
2
3

3
baker
mayfield
0
4


Warning: Null value is eliminated by an aggregate or other SET operation.