Return number of Employees per year by appointment status

Question

I have a table with columns as follows:

+-----------+--------------------+---------------+-------------+
| person_id | appointment_status | starting_date | ending_date |
+-----------+--------------------+---------------+-------------+
|     1     |          P         |   2011-11-23  | 2012-11-23  |
+-----------+--------------------+---------------+-------------+
|     2     |         JO         |   2011-11-23  | 2012-11-23  |
+-----------+--------------------+---------------+-------------+

Based on the table, I wanted to output the total number of the person registered in a certain year group by their status. Basically something like this:

+-----------+---------------+------+
|   Status  | No. of Person | Year |
+-----------+---------------+------+
| Permanent |       5       | 2011 |
+-----------+---------------+------+
| Job Order |       16      | 2011 |
+-----------+---------------+------+
| Permanent |       10      | 2012 |
+-----------+---------------+------+
| Job Order |       19      | 2012 |
+-----------+---------------+------+

Assuming that in the year 2011, there are about 5 registered persons(employees) who are permanent and 16 on job orders. Then following year 2012, there are 10 permanent and 19 job orders.

Answer 1

Try this:

SELECT CASE appointment_status 
            WHEN 'P' THEN 'Permanent '
            WHEN 'JO' THEN 'Job Order'
      END  AS [Status]
      ,COUNT(person_id) AS [No. of Person]
      ,YEAR(starting_date) AS [Year]
FROM [my_table] 
GROUP BY CASE appointment_status 
            WHEN 'P' THEN 'Permanent '
            WHEN 'JO' THEN 'Job Order'
         END    
        ,YEAR(starting_date);