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
x
+------------------+--------+-------------+-----------------+
| 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!
Advertisement
Answer
SELECT *
FROM Employee
WHERE PersonalNumber NOT IN (
SELECT PersonaNumber
FROM Employee
WHERE MonthWhenJoined =
SELECT Month
FROM currentDate
)