Mysql query data transformation

Question

I am trying to do transformation on a table in Mysql. I can't figure out how to do it. Could anyone tell me how to do it? The input and output is given. I would like to know how it is done?

Input table

+-------------+------------+------------------+-------------------+
| Employee_ID | Start_Date | Termination_Date | Performance_Level |
+-------------+------------+------------------+-------------------+
|           1 | 1/1/2007   | 3/1/2007         | Low               |
|           2 | 6/5/2004   | Null             | Medium            |
|           3 | 4/3/2003   | Null             | High              |
|           4 | 9/1/2002   | 4/15/2007        | Medium            |
|           5 | 4/6/2007   | 11/1/2007        | Low               |
|           6 | 7/1/2007   | Null             | High              |
|           7 | 3/2/2005   | 8/1/2007         | Low               |
+-------------+------------+------------------+-------------------+

Ouput Table

+---------+-----------------------------------+-----------------+-------------------+----------------+
| Period  |   Total_Employees_at_end_of_quarter | High_Performers | Medium_Performers | Low_Performers |
+---------+-----------------------------------+-----------------+-------------------+----------------+
| Q1-2007 |                                 4 |               1 |                 2 |              1 |
| Q2-2007 |                                 4 |               1 |                 1 |              2 |
| Q3-2007 |                                 4 |               2 |                 1 |              1 |
| Q4-2007 |                                 3 |               2 |                 1 |              0 |
+---------+-----------------------------------+-----------------+-------------------+----------------+

This is what I tried

select * from emp 
where date(sdate)< date'2007-04-01' and (date(tdate)> date'2007-03-31' or tdate is null);

select * from emp 
where date(sdate)< date'2007-07-01' and (date(tdate)> date'2007-06-30' or tdate is null);

select * from emp 
where date(sdate)< date'2007-010-01' and (date(tdate)> date'2007-09-30' or tdate is null);

select * from emp 
where date(sdate)< date'2008-01-01' and (date(tdate)> date'2007-12-31' or tdate is null);

I have the individual queries but I want a single query which will give the outputs.

Answer 1

try this

SELECT QUARTER('2008-04-01');

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_quarter

and CONCAT()

Answer 2

The approach taken below is to create a driver table for each quarter, with information about the year and quarter. This is then joined to the employee table, using a non-equijoin. Employees who start in or before the quarter and end after the quarter are active at the end of quarter.

It uses one trick for the date comparisons, which is to convert the year-quarter combination into a quarter count, by multiplying the year by 4 and adding the quarter. This is a convenience for simplifying the date comparisons.

select driver.qtryr, count(*) as TotalPerformers,
       sum(Performance_level = 'High') as HighPerformers,
       sum(Performance_level = 'Medium') as MediumPerformers,
       sum(Performance_level = 'Low') as LowPerformers
from (select 2007 as yr, 1 as qtr, 'Q1-2007' as qtryr union all
      select 2007 as yr, 2 as qtr, 'Q2-2007' as qtryr union all
      select 2007 as yr, 3 as qtr, 'Q3-2007' as qtryr union all
      select 2007 as yr, 4 as qtr, 'Q4-2007' as qtryr 
     ) driver left outer join
     Table1 emp
     on year(emp.start_date)*4+quarter(emp.start_date) <= driver.yr*4+qtr and
        (emp.termination_date is null or
         year(emp.termination_date)*4+quarter(emp.termination_date) > driver.yr*4+qtr
        )
group by driver.qtryr

• sqlfiddle