Get Employees records below particular month and year(Get based on month and Year)-CodePudding

I have a condition, where I want to get all employees list who are registered below Jan 2022. I want to get employees list who are Active, and who are Inactive till Jan 2022. I want to get based on month and year only. I have tired with my below query but not getting proper results. Any suggestions please.

SELECT A.id,A.first_name,A.status,B.join_date FROM employee AS A INNER JOIN employee_job_info AS B ON A.id = B.employee_id WHERE YEAR(B.join_date)<=2022 AND MONTH(B.join_date)<=1;

Below is my sql fiddle link http://sqlfiddle.com/#!9/e329fc/1

CodePudding user response：

To be honest, the YEAR()/MONTH() extraction is not necessary. As for the status, I don't see it being a significant condition required here. Nonetheless, I'm concerned that you may aiming for something else here instead of just a simple count. But if you really just want the count, based on your condition, then this query should be enough:

SELECT COUNT(id) as count
FROM employee
WHERE added_on <= '2022-01-31'
  AND last_active_date <= '2022-01-31';

CodePudding user response：

I've modified your where because you were saying that it had to be a January if any year up to 2022.
It is simpler to say `<='2022-01-31' I've also modified that schema set up because no data was being loaded.
Lastly I've added status in the SELECT and in a GROUP BY.

 CREATE TABLE  employee 
( 
  id int NOT NULL, 
  name varchar(100) NOT NULL, 
  added_on date NOT NULL, 
  last_active_Date date , 
  status int NOT NULL
);



INSERT INTO employee (id, name, added_on, last_active_Date, status) 
  VALUES (1,'David','2017-05-02','0000-00-00',1),
         (2,'John','2020-01-03','2022-01-04',0),
         (3,'Carl','2019-01-03','0000-00-00',1), 
         (4,'Rose','2021-01-08','2022-02-06',0),
         (5,'Rose','2021-11-08','2022-01-10',0),
         (6,'Rose','2020-09-08','0000-00-00',1),
         (7,'Rose','2019-01-08','0000-00-00',1),
         (8,'Rose','2020-08-08','0000-00-00',1),
         (9,'Rose','2020-01-08','0000-00-00',1),
         (10,'Rose','2018-05-08','0000-00-00',1);

✓

✓

select COUNT(id) as count
from employee
WHERE (year(added_on) <= '2022' 
   and MONTH(added_on) <= '01' 
   and status=1)
   or (status = '0' 
   and ((year(last_active_date)
   <= '2022') and
                        
   (month(last_active_date) <= '01')))

| count |
| ----: |
|     5 |

SELECT * FROM employee

id | name  | added_on   | last_active_Date | status
-: | :---- | :--------- | :--------------- | -----:
 1 | David | 2017-05-02 | 0000-00-00       |      1
 2 | John  | 2020-01-03 | 2022-01-04       |      0
 3 | Carl  | 2019-01-03 | 0000-00-00       |      1
 4 | Rose  | 2021-01-08 | 2022-02-06       |      0
 5 | Rose  | 2021-11-08 | 2022-01-10       |      0
 6 | Rose  | 2020-09-08 | 0000-00-00       |      1
 7 | Rose  | 2019-01-08 | 0000-00-00       |      1
 8 | Rose  | 2020-08-08 | 0000-00-00       |      1
 9 | Rose  | 2020-01-08 | 0000-00-00       |      1
10 | Rose  | 2018-05-08 | 0000-00-00       |      1

SELECT status,COUNT(*)
FROM employee
WHERE ( added_on <= '2022-01-31'
         AND status = 1)
  OR
       last_active_date <= '2022-01-31'
GROUP BY status

status | COUNT(*)
-----: | -------:
     0 |        2
     1 |        7

db<>fiddle here