Search code examples
mysqlselectdate-comparison

Get count from MySQL table which added on or before a date and deleted on or after a date

I am very much new to SQL and I don't know much

TABLE employee_table contains the following columns

`pkId` INT(10),
`name` VARCHAR(60),
`pfStatus` TINYINT(3),
`is_active` TINYINT(3),
`addedDate` DATE,
`deletedDate` DATE

And an entry is there with the given values

pkId = 1, name = abhips, pfStatus = 0, is_active = 1, addedDate = 2012-08-08, deletedDate = 2012-08-18

-

SELECT COUNT(*) FROM employee_table WHERE pfStatus = 0 AND addedDate >= '2012-07-07' AND deletedDate <= '2012-08-09';

I want to get the number of employees who are registered on or before a particular date and deleted on or after a particular date and pfStatus = 0.

But I am getting the count as 0 instead of 1 , please help me with this query, What are the changes to be made to get this query correct ?

Solution

  • Your <= and >= are the wrong way around.

    I want to get the number of employees who are registered on or before a particular date and deleted on or after a particular date and pfStatus = 0.

    Try this:

    SELECT COUNT(*)
FROM employee_table
WHERE pfStatus = 0
AND addedDate <= '2012-07-07'
AND deletedDate >= '2012-08-09';