计算员工在开始和结束日期之间报告工作的天数

作者: polo
发布时间: 2024-01-20 04:29:25 (7月前)
转自：

3 条回复

0#
回复此人
薄情 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <pre> <code> select c.DEPT_ID, c.QUARTER, c.CALENDAR_DATE, e.EMPLOYEE_ID, case when c.CALENDAR_DATE between e.TRANSACTION_DATE and NVL(t.TRANSACTION_DATE, c.CALENDAR_DATE) then 1 else 0 end ATTENDANCE_EXPECTED, CASE when c.IS_OPEN = 'x' then 1 else 0 end is_open from calendar c LEFT JOIN transactions e ON c.dept_id = e.dept_id and c.quarter = e.quarter and e.transaction_type = 4 left JOIN transactions t ON t.transaction_type = 5 and t.EMPLOYEE_ID = e.EMPLOYEE_ID; </code> </pre> <P> 查看更多行，您可以在此处过滤和计算非开放日期 <a href="https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d7114c71894defc35d98f4e7f8a2370b" rel="nofollow noreferrer"> 演示 </A> </p> </DIV>

编辑
1#
回复此人
大黑骡子王 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 这可能会奏效 </p> <P> 解释： <BR/> - 查找部门的所有开放日期，关闭日期不感兴趣 <BR/> - 加入该部门的所有员工/交易，如果 <BR/> ---日历日期＆gt; =类型4的转换日期 <BR/> ---和calender_date＆lt;转换日期类型为5 <BR/> - 公寓中的员工是交易表中类型为4的交易 </p> <pre> <code> 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); </code> </pre> </DIV>

编辑

登录后才能参与评论