Query to select an employee's Work Schedule and PTO (Tested using Oracle DB)

Using my last post of how to breakout an employee's PTO request into durations, we can now use that SQL as a basis of seeing the how picture of the employee's schedule.  If we use the employee's assigned schedule and join it to schedule calendar Table, this will break out the employee Time and Labor Schedule by duration.  If we go a step further and join the employee's holiday schedule from the job record, then we can account for Holiday's.  Now all we need to get a clear picture of the employee's schedule is the PTO Request broken up by duration. 


In the SQL below the PS_ZZ_PSFT_PTO is a populated via the sql the previous post.


WITH Schedule ( Emplid, Empl_Rcd, Dur, Wrkday_Id, Daynum, Hol_Descr) AS (
 SELECT E.EMPLID
 , E.EMPL_RCD
 ,DUR
 , WRKDAY_ID
 , DAYNUM
 , (
 SELECT H.DESCR
  FROM PS_HOLIDAY_DATE H
 WHERE H.HOLIDAY = DUR
   AND H.HOLIDAY_SCHEDULE =A.HOLIDAY_SCHEDULE)
  FROM Ps_Sch_Assign E, Ps_Sch_Clnd_Dtl_Vw F, Ps_JOB A
 WHERE E.Emplid = A.Emplid
   AND E.Empl_Rcd = A.Empl_Rcd
   AND A.EFFDT = (
 SELECT MAX(A_ED.EFFDT)
  FROM PS_JOB A_ED
 WHERE A.EMPLID = A_ED.EMPLID
   AND A.EMPL_RCD = A_ED.EMPL_RCD
   AND A_ED.EFFDT <= SYSDATE)
   AND A.EFFSEQ = (
 SELECT MAX(A_ES.EFFSEQ)
  FROM PS_ZZ_JOB_CUST A_ES
 WHERE A.EMPLID = A_ES.EMPLID
   AND A.Empl_Rcd = A_Es.Empl_Rcd
   AND A.EFFDT = A_ES.EFFDT)
   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)) )
 SELECT s.Emplid
 , s.Empl_Rcd
 , s.Dur
 , s.Wrkday_Id
 , s.Daynum
 , s.Hol_Descr
 , P.zz_requested
 , P.SCHED_HRS
 ,to_char(S.DUR
 ,'dd')
 , CASE NVL(HOL_DESCR
 ,'NULL') WHEN 'NULL' THEN CASE WRKDAY_ID WHEN 'OFF' THEN 'O' ELSE CASE WHEN NVL(ZZ_REQUESTED
 ,0) > 0 THEN CASE WHEN SCHED_HRS > ZZ_REQUESTED THEN 'P' ELSE 'A' END ELSE 'S' END END ELSE 'H' END
 ,'<td style="background-color:'||CASE NVL(HOL_DESCR
 ,'NULL') WHEN 'NULL' THEN CASE WRKDAY_ID WHEN 'OFF' THEN 'WHITE' ELSE CASE WHEN NVL(ZZ_REQUESTED
 ,0) > 0 THEN CASE WHEN SCHED_HRS > ZZ_REQUESTED THEN 'YELLOW;color:black' ELSE 'RED;color:white' END ELSE 'GREEN;color:white' END END ELSE 'BLUE;color:white;" title="'||replace(HOL_DESCR
 ,chr(39)
 ,'&#039;')||'' END||'"><div style=''width:30px;height:20;''>'||to_char(S.DUR
 ,'dd')||'</td>'
 ,'<td '||CASE NVL(HOL_DESCR
 ,'NULL') WHEN 'NULL' THEN '' ELSE 'title="'||replace(HOL_DESCR
 ,chr(39)
 ,'&#039;')||'" ' END || 'style="background-image:url('''
 , '</td>'
  FROM Schedule S LEFT OUTER JOIN PS_ZZ_PSFT_PTO P ON P.EMPLID = S.EMPLID
   And P.Bgn_Dt = S.Dur
    ORDER BY s.dur;




Using this Query I created a view and used the SQL to generate the data for an HTML table that allows managers and associates to view their teams schedule.  It will even let manager's drill down into indirect reports schedules to view availability of their indirect reports team's.