How to get dates based on months that appear more than once?

Question

I'm trying to get months of Employees' birthdays that are found in at least 2 rows

I've tried to unite birthday information table with itself supposing that I could iterate through them abd get months that appear multiple times There's the question: how to get birthdays with months that repeat more than once?

SELECT DISTINCT e.EmployeeID, e.City, e.BirthDate
FROM Employees e
GROUP BY e.BirthDate, e.City, e.EmployeeID
HAVING COUNT(MONTH(b.BirthDate))=COUNT(MONTH(e.BirthDate))
UNION
SELECT DISTINCT b.EmployeeID, b.City, b.BirthDate
FROM Employees b
GROUP BY b.EmployeeID, b.BirthDate, b.City
HAVING ...

Given table:

| 1 | City1 | 1972-03-26|
| 2 | City2 | 1979-12-13|
| 3 | City3 | 1974-12-16|
| 4 | City3 | 1979-09-11|  

Expected result :

| 2 | City2 |1979-12-13|
| 3 | City3 |1974-12-16|

Answer 1

Think of it in steps.

First, we'll find the months that have more than one birthday in them. That's the sub-query, below, which I'm aliasing as i for "inner query". (Substitute MONTH(i.Birthdate) into the SELECT list for the 1 if you want to see which months qualify.)

Then, in the outer query (o), you want all the fields, so I'm cheating and using SELECT *. Theoretically, a WHERE IN would work here, but IN can have unfortunate side effects if a NULL comes back, so I never use it. Instead, there's a correlated sub=query; which is to say we look for any results where the month from the outer query is equal to the months that make the cut in the inner (correlated sub-) query.

When using a correlated sub-query in the WHERE clause, the SELECT list doesn't matter. You could put 1/0 and it won't throw an error. But I always use SELECT 1 to show that the inner query isn't actually returning any results to the outer query. It's just there to look for, well, the correlation between the two data sets.

SELECT
  *
FROM
  @table AS o
WHERE 
  EXISTS
    (
      SELECT
        1
      FROM
        @table AS i
      WHERE
        MONTH(i.Birthdate) = MONTH(o.Birthdate)
      GROUP BY 
        MONTH(i.Birthdate)
      HAVING
        COUNT(*) > 1
    );