This is a bit of a workaround but should get what you are after if I understand your question right:
SELECT
YEAR(A.AppointmentDate) AS [Year],
CASE WHEN ATS.AppointmentSourceID in (1,2) THEN ' Call Center' ELSE 'Web' END AS AppointmentSource,
MONTH(A.Appointmentdate) AS MonthNumber,
COUNT(A.AppointmentID) AS NumberOfAppointments
FROM
Appointment A
INNER JOIN
AppointmentStatus AST ON AST.AppointmentStatusID = A.AppointmentStatusID
INNER JOIN
AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID
WHERE
YEAR(A.AppointmentDate) IN (2021, 2020)
--AND ATS.AppointmentSourceID IN (1, 3)
AND A.AppointmentStatusID = 1 -- Active
GROUP BY
YEAR(A.AppointmentDate), MONTH(A.AppointmentDate),
CASE WHEN ATS.AppointmentSourceID in (1,2) THEN ' Call Center' ELSE 'Web' END
Basically we ignored the lookup table here and hardcoded the values in a case statement to group the two appointment sources into one.
Alternative option if you have too many lookup values is another workaround in the join condition:
SELECT
YEAR(A.AppointmentDate) AS [Year],
ATS.[Description] AS AppointmentSource,
MONTH(A.Appointmentdate) AS MonthNumber,
COUNT(A.AppointmentID) AS NumberOfAppointments
FROM
Appointment A
INNER JOIN
AppointmentStatus AST ON AST.AppointmentStatusID = CASE when A.AppointmentStatusID in (1,2) then 1 else A.AppointmentStatusID end --checking if AppointmentStatusID is 1 or 2 then I am passing 1 to the join condition
INNER JOIN
AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID
WHERE
YEAR(A.AppointmentDate) IN (2021, 2020)
--AND ATS.AppointmentSourceID IN (1, 3)
AND A.AppointmentStatusID = 1 -- Active
GROUP BY
YEAR(A.AppointmentDate), MONTH(A.AppointmentDate),
ATS.[Description], AST.AppointmentStatusName
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…