CASE-Statement in WHERE-Clause | SQL

Question

Hi I have following Table with the current month 'Month':

+---------------+
| current_Month |
+---------------+
|            12 |
+---------------+

And I have another Table with workers 'Workers'

+--------+--------------------------+
|  Name  | Month_joined_the_company |
+--------+--------------------------+
| Peter  |                       12 |
| Paul   |                        9 |
| Sarah  |                        5 |
| Donald |                       12 |
+--------+--------------------------+

I now want, based on my Month table, Display all workers which joined the company untill the previous month
the current month is 10 I would like to have this output

+--------+--------------------------+
|  Name  | Month_joined_the_company |
+--------+--------------------------+
| Paul   |                        9 |
| Sarah  |                        5 |
+--------+--------------------------+

But on the end of the year, I would like to include all workers even thos which month is equal with the current month

+--------+--------------------------+
|  Name  | Month_joined_the_company |
+--------+--------------------------+
| Peter  |                       12 |
| Paul   |                        9 |
| Sarah  |                        5 |
| Donald |                       12 |
+--------+--------------------------+

I now have this Statement, but it does not work...

SELECT * 
FROM workers 
WHERE     
  CASE
        WHEN (SELECT TOP (1) Current_Month FROM Month) = 12
        THEN (Month_joined_the_company <= (SELECT TOP (1) Current_Month FROM Month))
        ELSE (Month_joined_the_company < (SELECT TOP (1) Current_Month FROM Month))
    END

But this does not work and I get an error. Can someone help me, how I can use CASE in a WHERE-Clause

Answer 1

Is this what you want?

select w.*
from workers w
inner join month m 
    on m.current_month = 12 
    or w.month_joined_the_company < m.current_month

This phrases as: if current_month = 12 then return all workers, else just return those whose month_joined_the_company is stricly smaller than current_month.

NB: you should probably consider use date datatypes to store these values, otherwise what happens when a new year begins?