我正在使用Oracle 12c。
我正在计算希望员工报告上班的天数,以最终报告每个季度的每个部门缺勤率。我已经将每个部门缺勤人日的虚拟表拼凑在一起,我想加入部门的预期人日(如果一个部门有3名员工,那么一个5天工作周的人数为3 x 5 = 15人-天)。
我有一张CALENDAR
桌子,告诉我部门是否开放;IS_OPEN
字段中的“ x”表示该部门已开放,并且其所有员工均应报告工作,其他任何情况均表示该部门已关闭。
CALENDAR(
DEPT_ID VARCHAR2(8) NOT NULL,
QUARTER VARCHAR2(8) NOT NULL,
CALENDAR_DATE DATE NOT NULL,
IS_OPEN VARCHAR2(1) NOT NULL
)
DEPT_ID | QUARTER | CALDR_DATE | IS_OPEN
--------|---------|------------|---------
ACCTING | 2019Q1 | 2019-03-02 | ' '
MFGING | 2019Q1 | 2019-03-02 | x
MRKTING | 2019Q1 | 2019-03-02 | ' '
ACCTING | 2019Q1 | 2019-03-03 | x
MFGING | 2019Q1 | 2019-03-03 | x
MRKTING | 2019Q1 | 2019-03-03 | x
ACCTING | 2019Q1 | 2019-03-04 | x
MFGING | 2019Q1 | 2019-03-04 | x
MRKTING | 2019Q1 | 2019-03-04 | x
...
我还有一个TRANSACTIONS
表格,告诉我员工的开始日期和结束日期(TRANS_TYPE
4是开始日期,TRANS_TYPE
5是结束日期)。
TRANSACTIONS(
DEPT_ID VARCHAR2(8) NOT NULL,
QUARTER VARCHAR2(8) NOT NULL,
TRANSACTION_DATE DATE NOT NULL,
TRANSACTION_TYPE NUMBER(1,0) NOT NULL,
EMPLOYEE_ID VARCHAR2(13) NOT NULL
)
DEPT_ID | QUARTER | TRANS_DATE | TRANS_TYPE | EMPLOYEE_ID
--------|---------|------------|------------|------------
...
MFGING | 2019Q1 | 2019-01-07 | 4 | 123
MRKTING | 2019Q1 | 2019-01-28 | 4 | 456
MFGING | 2019Q1 | 2019-02-01 | 5 | 123
...
在上表中,员工123从1月7日开始工作,并于2月1日离开部门。456号员工于1月28日开始工作,至今仍在该部门工作。
从上面的示例中不明显:上一季度曾是部门成员的任何员工在该季度TRANS_TYPE
的第一天出现在工作场所时,他们将自动获得4分(他们TRANS_TYPE
在最后一天没有得到5分)上一季度)。
我想加入这两个表,使得生成的虚拟表具有以下字段:DEPT_ID
,QUARTER
,CALDR_DATE
,EMPLOYEE_ID
,ATTENDANCE_EXPECTED
,DEPT_IS_OPEN
(可选),其中ATTENDANCE_EXPECTED
字段包含一个“1”,如果雇员预计工作报告的那一天(即部门是开放的,并且CALDR_DATE
介于员工的开始日期和结束日期之间(SYSDATE
如果没有结束日期,则介于之间)。
然后,我可以SUM(ATTENDANCE_EXPECTED)
(或COUNT(*)
在筛选后DEPT_IS_OPEN
)从结果表中进行分组DEPT_ID
,QUARTER
以得到每个部门和每个季度的人日数。
我无法弄清楚如何扩展行,以便从开始日期到结束日期(如果没有结束日期,则为当前日期)每天每员工每天有一行。
我该怎么做,或者有更好的方法来计算每个部门每季度的人日?
谢谢。
这可能有效
说明:
-查找部门的所有开放日,无休日无意义
-如果
日历日期> =类型4的转换日期
---和日历日期<类型5的转换日期,则加入该部门的所有员工/事务
-一个公寓中的一个雇员正在执行交易表中类型为4的交易
select c.DEPT_ID, c.QUARTER, c.CALENDAR_DATE, e.EMPLOYEE_ID, 1 ATTENDANCE_EXPECTED, c.IS_OPEN
from calendar c,
transactions e
where c.dept_id = e.dept_id
and c.quarter = e.quarter
and c.IS_OPEN = 'x'
and e.transaction_type = 4
and c.calendar_date < NVL((select transaction_date from transactions where dept_id = e.dept_id and Employee_Id = e.employee_id and quarter = e.quarter and transaction_type = 5),sysdate)
and c.calendar_date >= (select transaction_date from transactions where dept_id = e.dept_id and Employee_Id = e.employee_id and quarter = e.quarter and transaction_type = 4);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句