mysql - Query Count for each year based on single date

Question

I am being asked to provide some reporting on Starter/Leavers based on information we are already collecting.

I am trying to figure out whether this can be done by the MySQL itself, or if I will need to make multiple calls.

At the simplest level our data structure is:

ID    psName     dtStart     dtLeave

(This is produced from multiple tables that pull in Office, Status, etc, which we might use for more complicated queries)

For a given date I can use the following Query to get the number of people in the company:

SELECT COUNT(*) FROM
(
  SELECT ID, psName, dtStart, dtLeave
  FROM people
) st
WHERE (dtStart <= "2016-06-04" ) 
AND (dtLeave >= "2016-06-04" OR dtLeave IS NULL) 

I would like to be able to feed in a date, for example "2016-04-06" and get a list list of counts (either as columns or rows) for that date historically i.e.

6/4/2016 200
6/4/2015 175
6/4/2014 150
6/4/2014 125
6/4/2013 100

Does this make sense, and if so, is it possible?

Answer 1

Notice that there is no start and end date, this query will scan all table records.

In fact, you can reduce your query to:

SELECT YEAR(dtStart) as 'year', COUNT(*) FROM
FROM people
WHERE MONTH(dStart) = 4 AND DAY(dStart) = 6
GROUP BY YEAR(dtStart);

UPDATE

To get people employed on the selected period:

SET @SelectedDate = '2016-04-06';

SELECT YEAR(dStart), COUNT(*), @SelectedDate FROM
(
  SELECT ID, psName, dtStart, dtLeave
  FROM people
  WHERE DATE_FORMAT(dtStart, '%m%d') <= DATE_FORMAT(@SelectedDate, '%m%d')
        AND DATE_FORMAT(dtleave, '%m%d') >= DATE_FORMAT(@SelectedDate, '%m%d')
) st
GROUP BY YEAR(dStart);