Search code examples
sqloracle-databaseplsqloracle12c

Counting the number of days an employee is expected to report for work between their start and end dates

I am using Oracle 12c.

I am looking to count the number of days an employee is expected to report to work, to ultimately report each department's absentee rate per quarter. I've already cobbled together a virtual table with each department's absentee person-days to which I would like to join the department's expected person-days (if a department has 3 employees, then a 5 day work week has 3 x 5 = 15 person-days).

I have a CALENDAR table that tells me if a department is open; an 'x' in the IS_OPEN field indicates that the department is open and all its employees are expected to report for work, anything else means the department is closed.

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
...

I also have a TRANSACTIONS table that tells me an employee's start date and end date (TRANS_TYPE 4 is a start date, TRANS_TYPE 5 is an end date).

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
...

In the above table, employee 123 started work on 7 January and left the department on 1 February. Employee 456 started on 28 January and is still in that department today.

Not obvious from the above example: any employee that was a member of a department in the previous quarter will automatically get a TRANS_TYPE 4 when they show up for work on the first day of the quarter (they don't get a TRANS_TYPE 5 on the last day of the previous quarter).

I would like to join these two tables such that the resulting virtual table has the following fields: DEPT_ID, QUARTER, CALDR_DATE, EMPLOYEE_ID, ATTENDANCE_EXPECTED, DEPT_IS_OPEN (optional), where the ATTENDANCE_EXPECTED field contains a '1' if the employee is expected to report for work that day (ie, the department is open and the CALDR_DATE is between the employee's start date and end date or SYSDATE if no end date exists).

I can then SUM(ATTENDANCE_EXPECTED) (or possibly COUNT(*) after filtering for DEPT_IS_OPEN) from the resulting table, and group by DEPT_ID, QUARTER to get the number of person-days for each department and quarter.

I can't figure out how to expand the rows so that I have one row per employee per day for every day from their start date to their end date (or current date if there is no end date).

How do I do this, or is there a better way to count person-days per department per quarter?

Thank you.

Solution

  • This might work

    Explained:
    - Find all open days for department, closed days are of no interest
    - Join all employees/transactions for that department if
    --- calender date >= trans date with type 4
    --- and calender_date < trans date with type 5
    - An employee in an apartment IS a transaction with type 4 in the transaction table

    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);