SQL Server group 2 conditions into one

Question

I have below SQL statement to get the appointments for 2020 & 2021:

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 = 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), 
    ATS.[Description], AST.AppointmentStatusName

Appointment source table (AppointmentSource) has below rows:

1. Call center
2. Agent
3. Web

I need to show count of Call Center + Agent as 'Call Center' and Web as 'Web'

Please help me.

question from:https://stackoverflow.com/questions/65891184/sql-server-group-2-conditions-into-one

Answer 1

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