SQL | Display all rows where date is not current month

Question

I have two tables
In one table there are my employees and when they changed the Department
In the second table there is my current date

Employee Table

+------------------+--------+-------------+-----------------+
| Personal Number  | Salary | Department  | MonthWhenJoined |
+------------------+--------+-------------+-----------------+
|              224 |   1000 | HR          |              03 |
|              224 |   1500 | R&D         |              07 |
|              578 |   1200 | Sales       |              04 |
|              578 |   2000 | Engineering |              09 |
|              694 |   1400 | R&D         |              04 |
|              694 |   1500 | Sales       |              08 |
+------------------+--------+-------------+-----------------+

Table with current Date

+------------+-----+-------+------+
|    Date    | Day | Month | Year |
+------------+-----+-------+------+
| 01.09.2019 |  01 |    09 | 2019 |
+------------+-----+-------+------+

Now I want to only see all Employee that have no 'MonthWhenJoined' equal to the current Month.
So the Result would be something like this

+------------------+--------+-------------+-----------------+
| Personal Number  | Salary | Departement | MonthWhenJoined |
+------------------+--------+-------------+-----------------+
|              224 |   1000 | HR          |              03 |
|              224 |   1500 | R&D         |              07 |
|              694 |   1400 | R&D         |              04 |
|              694 |   1500 | Sales       |              08 |
+------------------+--------+-------------+-----------------+

I know it can not be that hard, but I cant figure it out …
Thank you for your help!

Your table with current date has been updated for more than 2 weeks. — jarlh, Sep 18 '19 at 07:17
Still makes sense. I'd never trust a stored current date in a table. Either create a stored procedure or a view returning the actual current date! — jarlh, Sep 18 '19 at 07:30
@jarlh, why does the current date in the table have to be the same as the day the query is run? — Jodrell, Sep 18 '19 at 07:39
@Jodrell, if it is another date _current date_ is very misleading... — jarlh, Sep 18 '19 at 07:41
@jarlh, if the table had the same name as some system function then yes, that would be confusing. However, perhaps you can imagine a situation where the current date of the system is different from today. — Jodrell, Sep 18 '19 at 07:48

Andoni Alzelai · Answer 1 · 2019-09-18T07:43:50.957

2

in this case I would join two tables putting those dates as different on it

SELECT * FROM Employee 
WHERE personalNumber NOT IN 
  (SELECT personalNumber 
   FROM Emplayee e 
   JOIN currentDate d ON e.MonthWhenJoined = d.month)

edited Sep 18 '19 at 07:43

answered Sep 18 '19 at 07:18

Andoni Alzelai

66
4

Thank you for your answer, but then I still get one row of 578 I want dont want to display a person, if he has moved in the current month – clem995 Sep 18 '19 at 07:24
I have edited the answer, i didn´t see that detail of not repeating the employee, try this new one – Andoni Alzelai Sep 18 '19 at 07:36
@clem995, [`SELECT *` is generally a bad idea](https://stackoverflow.com/a/3639964/659190), additionally, as a personal preference, I don't like the use of avoidable subqueries but that would not effect the query plan. – Jodrell Sep 18 '19 at 08:01

apomene · Accepted Answer · 2019-09-18T07:39:51.693

1

SELECT *
FROM Employee
WHERE PersonalNumber NOT IN (
        SELECT PersonaNumber
        FROM Employee
        WHERE MonthWhenJoined =
        SELECT Month
        FROM currentDate
        )

edited Sep 18 '19 at 07:39

answered Sep 18 '19 at 07:33

apomene

14,282
9
46
72

@clem995, [`SELECT *` is generally a bad idea](https://stackoverflow.com/a/3639964/659190), additionally, as a personal preference, I don't like the use of avoidable subqueries but that would not effect the query plan. – Jodrell Sep 18 '19 at 07:59

Jodrell · Answer 3 · 2019-09-18T07:50:33.760

0

Simple,

SELECT
         E.[Personal Number],
         E.[Salary],
         E.[Department],
         E.[MonthWhenJoined]
   FROM
         [someSchema].[Employee] E
      LEFT JOIN
         [someSchema].[CurrentDate] C
            ON C.[Month] = E.[MonthWhenJoined]
   WHERE
         C.[Date] IS NULL;

of course, there is no way to tell if that month was in the same year as the current date.

edited Sep 18 '19 at 07:50

answered Sep 18 '19 at 07:45

Jodrell

34,946
5
87
124

score 0 · Answer 4 · answered Sep 18 '19 at 08:04

0

Simple:

SELECT * FROM employee WHERE `MonthWhenJoined` NOT IN(SELECT `Month` FROM date WHERE    `Year` = YEAR(CURDATE()));

answered Sep 18 '19 at 08:04

Ravi Ranjan

269
3
2

@clem995, [`SELECT *` is generally a bad idea](https://stackoverflow.com/a/3639964/659190), additionally, as a personal preference, I don't like the use of avoidable subqueries but that would not effect the query plan. – Jodrell Sep 18 '19 at 08:16

SQL | Display all rows where date is not current month

4 Answers4