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;
查看更多行,您可以在此处过滤和计算非开放日期 演示
这可能会奏效
解释: - 查找部门的所有开放日期,关闭日期不感兴趣 - 加入该部门的所有员工/交易,如果 ---日历日期> =类型4的转换日期 ---和calender_date<转换日期类型为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);