MySQL - Loop while -

i´ve been asked create report shows how many employees working in different departments during last trailing 12 months (mm-yyyy).

i need print out every single month every combination of department, role_name , employee_code.

i want print out results this: (assuming employee 234 joined company on sept 15 jr consultant in bsg department, promoted consultant in jan 16 , changed department in apr 16)

date    department role_name    employee_code jun 16  cin        consultant   234 mai 16  cin        consultant   234 apr 16  cin        consultant   234 mrz 16  bsg        consultant   234 feb 16  bsg        consultant   234 jan 16  bsg        consultant   234 dez 15  bsg       jr consultant 234 nov 15  bsg       jr consultant 234 okt 15  bsg       jr consultant 234 sep 15  bsg       jr consultant 234 

attached code.

drop procedure if exists whileloop; procedure whileloop()  begin   declare date.date char;    whileloop : loop        if (date.date < date_format(prs.rpr_end_dt,'%y-%m'))       leave whileloop;     end if;      set date.date = date.date;   end loop whileloop;  end;   select date.date ,prs.employee_id ,prs.department ,prs.role_name    ,case         when prs_person_end_dt < curdate() 'no'         else 'yes'     end 'still company?'      ,case         when prs_person_end_dt < curdate() concat(month(prs_person_end_dt), ' - ', year(prs_person_end_dt))         else ''     end quitdate     dim_prs_person prs          /*used getting month/year*/     inner join (         select date_format(dys_date,'%y-%m') date         lkp_dys_days         group date_format(dys_date,'%y-%m')         ) date on date_format(prs.rpr_start_dt,'%y-%m') = date.date   /*where date.date between date_sub(curdate() ,interval 12  month ) , curdate()*/  order     prs.pma_person_code     ,date.date; 

the results this:

date     employee_code  department  role_name   still company? quitdate 2015-12     abna             off    werkstudent         quit         mai 16 2013-02     admn             off    werkstudent      2013-02     admn             eco    consultant       2014-08     admn             off    external partner         2007-09     alde             ecn    consultant          quit          jun 12 2013-04     begd             cin    consultant       2015-10     laue             bso    werkstudent         quit          jan 16 2012-10     pore             cin    praktikant       2013-01     pore             cin    junior consultant        2014-07     pore             bpn    consultant       

as can see, months not repeated each employee code, department , role name. example, first employee (abna), want print of every single month until may 16, in month person quit.

this example of lkp_dys_days table:

dys_month   dys_year    dys_date 7            2004       01.07.2004 7            2004       02.07.2004 7            2004       03.07.2004 7            2004       04.07.2004 7            2004       05.07.2004 

this example of dim_prs_person table:

employee_id prs_person_start_dt prs_person_end_dt   department  role_name boda          01.07.2004       30.06.2007            off    jr consultant boda          01.07.2007       31.12.2099            off    consultant mele          01.07.2004       30.06.2007            cin    consultant mele          01.07.2007       31.07.2009            bsd    sr consultant oida          01.10.2004       30.09.2008            cin    consultant emed          01.11.2004       30.11.2006            cin    sr consultant dkel          02.11.2004       30.09.2009            bsd    werkstudent dhje          01.12.2004       30.05.2016            bsd    jr consultant dheh          24.01.2005       23.05.2005            eco    jr consultant meine         01.04.2005       31.08.2007            tde    consultant 

thank you!

if understand table lkp_dys_days correctly list of existing days (you need that), can use

select concat(d.dys_year, '-', lpad(d.dys_month, 2, '0')) date,   prs.department,   prs.role_name,   prs.employee_id dim_prs_person prs join lkp_dys_days d on prs.rpr_start_dt <= d.dys_date     , prs.prs_person_end_dt >= d.dys_date d.dysdate > date_sub(curdate(), interval 12 month)       , d.dys_date <= curdate() group d.dys_year, d.dys_month, prs.employee_id,           prs.prs_person_start_dt, prs.department, prs.role_name order d.dys_year desc, d.dys_month desc,          prs.employee_id, prs.prs_person_start_dt; 

if want show whole year if person wasn't there yet, can use right outer join instead of join.