MySQL - 循环而

Question

我被要求创建一份报告，显示过去 12 个月 (MM-YYYY) 中不同部门的员 worker 数。

我需要每个月打印出 Department、Role_Name 和 Employee_Code 的每个组合。

我想打印出如下所示的结果:(假设员工234于9月15日加入公司，担任BSG部门的Jr Consultant，1月16日晋升为顾问，4月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

附上我的代码。

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')) THEN
      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() THEN 'No'
        ELSE 'Yes'
    END as 'Still with Company?'

    ,CASE
        WHEN prs_person_end_dt < curdate() THEN concat(month(prs_person_end_dt), ' - ', year(prs_person_end_dt))
        ELSE ''
    END as QuitDate

FROM
   dim_prs_person prs

        /*Used for getting the month/year*/
    inner join (
        select date_format(dys_date,'%Y-%m') as date
        from lkp_dys_days
        group by 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 ) AND CURDATE()*/

ORDER BY
    prs.PMA_PERSON_CODE
    ,date.date;

目前的结果是这样的:

Date     Employee_code  Department  Role_Name   Still with 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      

如您所见，目前每个员工代码、部门和角色名称都不会重复所有月份。例如，对于第一个员工 (ABNA)，我想要打印直到 5 月 16 日为止的每个月，也就是这个人离职的那个月。

这是 lkp_dys_days 表的示例:

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

这是 dim_prs_person 表的示例:

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

谢谢!

Answer 1

如果我正确理解你的表 lkp_dys_days 作为所有现有日期的列表(你需要类似的东西)，你可以使用

select concat(d.dys_year, '-', lpad(d.dys_month, 2, '0')) as date,
  prs.DEPARTMENT,
  prs.ROLE_NAME,
  prs.EMPLOYEE_ID
from dim_prs_person prs
join lkp_dys_days d
on prs.rpr_start_dt <= d.dys_date 
   and prs.prs_person_end_dt >= d.dys_date
where d.dysdate > date_sub(curdate(), interval 12 month)
      and d.dys_date <= curdate()
group by d.dys_year, d.dys_month, prs.EMPLOYEE_ID, 
         prs.PRS_PERSON_START_DT, prs.DEPARTMENT, prs.ROLE_NAME
order by d.dys_year desc, d.dys_month desc,
         prs.EMPLOYEE_ID, prs.PRS_PERSON_START_DT;

如果你想显示整年，即使这个人还不在，你可以使用 right outer join 而不是 join。