Combine CASE expression in JOIN Clause involving different comparison operators

Question

I have two SELECT statements and want to combine them into one JOIN statement based on the input param: @UseFirstOne = true. If @UseFirstOne is true then we join on the first condition and if false, then we join on the second condition.

1.

    JOIN X.[Employee] emp 
     ON emp.EmployeeId = @EmployeeId
     AND emp.BirthYear = @BirthYearFromParam

    JOIN X.[Employee] emp 
     ON emp.EmployeeId = @EmployeeId
     AND emp.BirthYear < @CurrentYear OR (emp.BirthYear = @CurrentYear AND emp.BirthMonth < @CurrentMonth)

How can I combine them into one JOIN statement?

Answer 1

You need to think algebraically, and use parenthesis to enforce the logic order you want. Consider:

DECLARE @UseFirstOne BIT = 0, @BirthYearFromParam INT = 2000

SELECT *
FROM Customers
WHERE 
    (
        DATEPART(YEAR,BirthDate) = @BirthYearFromParam 
        AND @UseFirstOne = 1
    )
    OR 
    (
        (
            DATEPART(YEAR,BirthDate) < DATEPART(YEAR,GETDATE())
            OR
            (
                DATEPART(YEAR,BirthDate) =  DATEPART(YEAR,GETDATE())
                AND DATEPART(MONTH,BirthDate) < DATEPART(MONTH,GETDATE())
            )
        )
        AND @UseFirstOne = 0
    )