Oracle 员工出勤报告

很抱歉问了这个看似微不足道的问题，但我已经为此苦苦挣扎了一段时间，我的最后期限快到了，我找不到任何答案。

我有3张表:
日历表:

CREATE TABLE  "CJ_CAL" 
   ( "CAL_ID" NUMBER NOT NULL ENABLE, 
 "CAL_DATE" DATE, 
  CONSTRAINT "CJ_CALENDAR_PK" PRIMARY KEY ("CAL_ID")
   )

员工表

CREATE TABLE  "CJ_EMP" 
   ( "EMP_ID" NUMBER NOT NULL ENABLE, 
 "FIRST_NAME" VARCHAR2(40), 
 "LAST_NAME" VARCHAR2(40), 
 "DEPARTMENT" VARCHAR2(10), 
 "STATION" VARCHAR2(10), 
 "MANAGER" VARCHAR2(40), 
 "WORK_HOURS" NUMBER, 
 "SCHEDULE" VARCHAR2(400), 
  CONSTRAINT "CJ_EMP_PK" PRIMARY KEY ("EMP_ID")
   )

和

出勤表

CREATE TABLE  "CJ_EMP_CAL" 
   ( "EMP_CAL_ID" NUMBER NOT NULL ENABLE, 
 "CAL_ID" NUMBER NOT NULL ENABLE, 
 "EMP_ID" NUMBER NOT NULL ENABLE, 
 "STATUS" NUMBER, 
 "OVERTIME" NUMBER, 
 "REMARK" VARCHAR2(400), 
  CONSTRAINT "CJ_EMP_CAL_PK" PRIMARY KEY ("EMP_CAL_ID")
   )

cj_emp_cal references cj_cal 和 cj_emp using a foreign key.

I need to create an interactive report where I will display all the employees in separate rows 和 have columns for each day of a selected month. All the employees have been 'assigned' a working day in the CJ_EMP_CAL table 和 I need to display the status (A - Absent, P - Present, OT - Overtime, V - vacation) in the report for each day 和 each employee respectively.
I also need to add two more columns as data for each employee (cj_emp.department 和 cj_emp.station).

感谢您成为出色的资源!

致以最诚挚的问候，
戈丹

因此，如果员工在CJ_EMP_CAL中没有条目，这是否意味着他们应该显示为缺席？还是别的什么？

无论如何，听起来您需要在时间范围内显示CJ_CAL中的所有行。然后其他表中的任何相关行。所以你需要把它们都结合在一起。

它给出了一个查询，比如:

select c.cal_date, s.stat_name, ec.emp_id, e.department , e.station
from   CJ_CAL c
left join CJ_EMP_CAL ec 
on    c.cal_id = ec.cal_id
and   ec.emp_id in (1, 2, 3, 4)
left join  CJ_EMP e
on    e.emp_id = ec.emp_id
left join CJ_STATUS s
on    ec.status = s.stat_id
where  c.cal_date >= date'2018-02-01'
and    c.cal_date < date'2018-02-08'
order  by ec.emp_id, c.cal_date;

CAL_DATE               STAT_NAME   EMP_ID   DEPARTMENT   STATION   
01-FEB-2018 06:08:04   OVERTIME           1 GSE          YHM       
02-FEB-2018 06:08:04   PRESENT            1 GSE          YHM       
03-FEB-2018 06:08:04   OVERTIME           1 GSE          YHM       
04-FEB-2018 06:08:04   ABSENT             1 GSE          YHM       
05-FEB-2018 06:08:04   OVERTIME           1 GSE          YHM       
06-FEB-2018 06:08:04   PRESENT            1 GSE          YHM       
07-FEB-2018 06:08:04   PRESENT            1 GSE          YHM       
01-FEB-2018 06:08:04   VACATION           2 GSE          YHM       
02-FEB-2018 06:08:04   ABSENT             2 GSE          YHM       
03-FEB-2018 06:08:04   OVERTIME           2 GSE          YHM       
04-FEB-2018 06:08:04   OVERTIME           2 GSE          YHM       
05-FEB-2018 06:08:04   ABSENT             2 GSE          YHM       
06-FEB-2018 06:08:04   PRESENT            2 GSE          YHM       
07-FEB-2018 06:08:04   OVERTIME           2 GSE          YHM       
01-FEB-2018 06:08:04   ABSENT             3 GSE          YHM       
02-FEB-2018 06:08:04   OVERTIME           3 GSE          YHM       
03-FEB-2018 06:08:04   OVERTIME           3 GSE          YHM       
04-FEB-2018 06:08:04   ABSENT             3 GSE          YHM       
05-FEB-2018 06:08:04   OVERTIME           3 GSE          YHM       
06-FEB-2018 06:08:04   PRESENT            3 GSE          YHM       
07-FEB-2018 06:08:04   VACATION           3 GSE          YHM       
01-FEB-2018 06:08:04   ABSENT             4 GSE          YVR       
02-FEB-2018 06:08:04   VACATION           4 GSE          YVR       
03-FEB-2018 06:08:04   PRESENT            4 GSE          YVR       
04-FEB-2018 06:08:04   OVERTIME           4 GSE          YVR       
05-FEB-2018 06:08:04   PRESENT            4 GSE          YVR       
06-FEB-2018 06:08:04   OVERTIME           4 GSE          YVR       
07-FEB-2018 06:08:04   ABSENT             4 GSE          YVR