Cool Absence Query for ORACLE

Absence management stores absence requests in blocks on the GP_ABS_EVENT table, so if you request one week of PTO it will store the entire week on one record using a from and through date.  Sometimes you need to have this block of PTO broken up into individual days and you have to take in account the employee's schedule.  This is because someone may request two weeks off and that block might include weekends and other non scheduled time and Absence will not include time off in its calculations. 


This query takes in account multiple absence takes, since we have a Volunteer Day where employee's can volunteer at a local charity, and usually these places have 4 hour shifts, so the employee will typically take the other half day as PTO, if they so choose. 

So I created a view of the Absence Request to sum the end day hours and begin day hours on the absence request.  This allows me to see if they are taking part of the day off or the whole day off without worrying about multiple takes, as I want my query to only pull back one duration per day.  To do that


This is the ZZ_ABS_EVENT View in the SQL

SELECT A.EMPLID
 , A.EMPL_RCD
 , A.BGN_DT
 , A.ALL_DAYS_IND
 , A.END_DT
 , SUM(A.END_DAY_HRS)
 , SUM(A.BEGIN_DAY_HRS)
  FROM Ps_Gp_Abs_event A
 WHERE A.VOIDED_IND = 'N'
   AND A.MANAGER_APPR_IND = 'Y'
  GROUP BY A.EMPLID, A.EMPL_RCD, A.BGN_DT, A.ALL_DAYS_IND, A.END_DT


Here is my SQL to select all approved absence request, broken up by day.

 SELECT  E.EMPLID
 , B.OPRID
 , F.Dur
 ,  WRKDAY_ID
 , SUM(CASE all_days_ind WHEN 'Y' THEN a.begin_day_hrs ELSE CASE WHEN f.dur = a.bgn_dt THEN DECODE(a.begin_day_hrs
 ,0
 ,F.SCHED_HRS
 ,a.begin_day_hrs) ELSE CASE WHEN a.end_dt = f.dur THEN DECODE(a.end_day_hrs
 ,0
 ,F.SCHED_HRS
 ,a.end_day_hrs) ELSE f.sched_hrs END END END)
 , F.Sched_Hrs 
  FROM   PS_SCH_ASSIGN E LEFT OUTER JOIN PS_ZZ_ABS_EVENT A ON A.EMPLID = E.EMPLID AND   A.EMPL_RCD = E.EMPL_RCD
  , Ps_Sch_Clnd_Dtl_Vw F
  , PSOPRDEFN B 
 WHERE F.DUR BETWEEN A.bgn_Dt AND a.end_dt 
   AND E.EMPLID = B.EMPLID   
   AND E.EFFDT = ( 
 SELECT MAX(E_ED.EFFDT) 
  FROM PS_SCH_ASSIGN E_ED 
 WHERE E.EMPLID = E_ED.EMPLID 
   AND E.EMPL_RCD = E_ED.EMPL_RCD 
   AND E_ED.EFFDT <= to_date(sysdate)) 
   AND F.SETID = E.SETID 
   AND F.SCH_ADHOC_IND = E.SCH_ADHOC_IND 
   AND F.SCHEDULE_ID = E.SCHEDULE_ID 
   AND F.ROTATION_ID = E.ROTATION_ID 
   AND F.EFFDT = ( 
 SELECT MAX(F_ED.EFFDT) 
  FROM PS_SCH_CLND_DTL_VW F_ED 
 WHERE F.SETID = F_ED.SETID 
   AND F.SCH_ADHOC_IND = F_ED.SCH_ADHOC_IND 
   AND F.SCHEDULE_ID = F_ED.SCHEDULE_ID 
   AND F.ROTATION_ID = F_ED.ROTATION_ID 
   AND F.DUR = F_ED.DUR 
   AND F.SHIFTNUM = F_ED.SHIFTNUM 
   AND F_ED.EFFDT <= to_date(sysdate)) 
   AND F.DUR >= A.BGN_DT 
   AND F.DUR <= A.END_DT 
   AND F.OFFDAY_IND = 'N' 
   And A.End_Dt >= To_Date(Sysdate) 
  group by E.EMPLID, B.OPRID, F.Dur, WRKDAY_ID, F.Sched_Hrs;