Hi,
Oracle 11.2.0.4 running on Linux.
I have a table which stores an employee's work history, including whether they were actively on the roster, and how many hours they were working. The table has approx 4 million rows and an average width of 800 characters.
I need to take a person at random from the table, and poll a rolling one year window based on todays date. I need to calculate the average daily hours in that window.
Rules
1) The system should create a window of trunc(sysdate) and add_months(trunc(sysdate)-12)
2) If the employee was not hired until the middle of the window, only calculate the average from their hire date (ie do not count the days prior to their hire as 0).
3) If the employee is ever inactive, start the average calculation from the beginning of their most recent active engagement
4) The result must be creatable in a view so that I can SELECT AVG_DAILY_HOURS FROM ( ... SQL ...) WHERE EMP_ID = :1
In the example below, I have three EMP_IDs
01010101 - always worked 8 hours a day. Should return an average of 8 hours.
02020202 - transitioned from 8 to 5, but was inactive after the transition and so should return an average of 5 hours - as this is their only daily hours since their return to work.
03030303 - transitioned from 8 to 5 and has always been active. This should show an average somewhere between 8 and 5 obviously the exact value depends on sysdate and ADD_MONTHS(sysdate,-12)
I am really struggling with how to cater for 02020202. My current approach is to try find the MAX(ASOFDATE) WHERE STATUS = 'I', and then NVL() it with the beginning of the time window to look for people who have been active throughout. But I am now joining the table into the SQL statement about 6 times getting myself all confused. I suspect that Analytics can do a far better job of this, but I can't seem to get the logic right. Does anyone have some pointers
create table MY_EXAMPLE as
select EMP_ID, ASOFDATE, 'A' "STATUS", 8.00 "DLY_HOURS", rpad('*',780,'*') "FILLER"
from (
select '01010101' "EMP_ID" from dual
union
select '02020202' from dual
union
select '03030303' from dual) EMPS
,
(select to_Date('01-JAN-2016', 'dd-mon-yyyy') + (rownum*5) "ASOFDATE"
from dual connect by level <= 20) ASOFDATES
/
UPDATE MY_EXAMPLE
SET STATUS = 'I' WHERE EMP_ID = '02020202' AND ASOFDATE = '21-JAN-2016'
/
UPDATE MY_EXAMPLE
SET DLY_HOURS = 5.0 WHERE EMP_ID IN ( '03030303' , '02020202') AND ASOFDATE > '15-JAN-2016'
/
COMMIT
/