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|

Please tag the correct RDBMS. I removed SSMS as the IDE is not relevant here. — Parfait, May 08 '19 at 20:54
Tag properly! MySQL and SQL Server are completely different product. Which one is this? — Eric, May 08 '19 at 22:12

score 0 · Answer 1 · answered May 08 '19 at 20:47

0

Seems to be an odd requirement.

This might help with some tweaks. Works in Oracle.

    SELECT DATE FROM TABLE WHERE EXTRACT(MONTH FROM DATE)=EXTRACT(MONTH FROM SOMEDATE);

answered May 08 '19 at 20:47

alexherm

1,362
2
18
31

score 0 · Answer 2 · edited May 08 '19 at 22:54

0

Give this a try and you may be able to dispense with your UNION:

SELECT 
   EmployeeId
 , City
 , BirthDate 
FROM Employees 
GROUP BY 
   EmployeeId
 , City
 , BirthDate 
HAVING COUNT(Month(BirthDate)) > 2

edited May 08 '19 at 22:54

Eric Brandt

7,886
3
18
35

answered May 08 '19 at 21:00

LeaderDesslok

1
2

score 0 · Accepted Answer · answered May 08 '19 at 21:14

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
    );

So the inner querry with EXISTS works like a loop that goes through all values of table by using helper table and returns true or false based on condition in WHERE ? I'm having a hard time adjusting to the fact that I can't make queries go like waterfall code in other programming languages. Any tips or advice on how I could start thinking logic in subqueries? — Vitaliy ehh, May 09 '19 at 09:42
SQL isn't an imperative programming language, it's declarative. In one sense, that means that a query describes what we want the results to "look" like (we declare the parameters of the set to return) and we let the SQL engine decide how to build that set. Here's an interesting description of the differences. https://stackoverflow.com/a/15358970/5790584 — Eric Brandt, May 09 '19 at 11:51

Radagast · Answer 4 · 2019-05-09T02:20:07.750

0

Here is another approach using GROUP_CONCAT. It's not exactly what you're looking for but it might do the job. Eric's approach is better though. (Note: This is for MySQL)

SELECT GROUP_CONCAT(EmployeeID) EmployeeID, BirthDate, COUNT(*) DupeCount 
FROM Employees 
GROUP BY MONTH(BirthDate) 
HAVING DupeCount> 1;

edited May 09 '19 at 02:20

answered May 08 '19 at 22:40

Radagast

5,102
3
12
27

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

4 Answers4