Query To Find Employees Who Have Taken More Than Their Eligible Leave With Respect To Their Job Roles

I have these tables with the following columns : Employee24( EMPLOYEEID, FIRSTNAME, LASTNAME, GENDER, JOBROLES); Leave25( EMPLOYEEID,LEAVEID, LEAVETYPE, STARTDATE, ENDDATE); JOBR

Solution 1:

This will find each user that has exceeded the leave amount for each type of leave:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATETABLE Employee24( EMPLOYEEID, JOBROLES ) ASSELECT1, 'RoleA'FROM DUAL UNIONALLSELECT2, 'RoleB'FROM DUAL UNIONALLSELECT3, 'RoleB'FROM DUAL;

CREATETABLE Leave25( EMPLOYEEID,LEAVEID, LEAVETYPE, STARTDATE, ENDDATE) ASSELECT1,1,'SickLeave',  DATE'2018-01-01', DATE'2018-01-11'FROM DUAL UNIONALLSELECT1,2,'SickLeave',  DATE'2018-01-21', DATE'2018-01-31'FROM DUAL UNIONALLSELECT1,3,'EarnedLeave',DATE'2018-01-11', DATE'2018-01-21'FROM DUAL UNIONALLSELECT1,4,'EarnedLeave',DATE'2018-02-01', DATE'2018-02-11'FROM DUAL UNIONALLSELECT1,5,'EarnedLeave',DATE'2018-02-21', DATE'2018-03-03'FROM DUAL UNIONALLSELECT2,6,'EarnedLeave',DATE'2018-02-01', DATE'2018-02-13'FROM DUAL UNIONALLSELECT3,7,'SickLeave',  DATE'2018-01-01', DATE'2018-01-09'FROM DUAL;


CREATETABLE JOBROLESELIGIBLELE(JOBROLES, ELIGIBLE_SICK_LEAVES, ELIGIBLE_EARNED_LEAVES) ASSELECT'RoleA', 14, 24FROM DUAL UNIONALLSELECT'RoleB',  7, 10FROM DUAL;

Query 1:

SELECT e.employeeId,
       l.leavetype,
       l.days_leave,
       r.AllowedLeaveAmount
FROM   Employee24 e
       INNER JOIN
       ( SELECT employeeId,
                SUM( enddate - startdate ) AS days_leave,
                leavetype
         FROM   Leave25
         GROUPBY employeeId, leaveType
       ) l
       ON ( e.employeeId = l.employeeId )
       INNER JOIN
       ( SELECT *
         FROM   JobRolesEligibleLE
         UNPIVOT ( AllowedLeaveAmount FOR LeaveType IN (
           Eligible_Sick_Leaves   AS'SickLeave',
           Eligible_Earned_Leaves AS'EarnedLeave'
         ) )
       ) r
       ON (    l.leavetype = r.leavetype
           AND e.jobroles   = r.jobroles )
WHERE  l.days_leave > r.AllowedLeaveAmount

Results:

| EMPLOYEEID |   LEAVETYPE | DAYS_LEAVE | ALLOWEDLEAVEAMOUNT |
|------------|-------------|------------|--------------------|
|          1 |   SickLeave |         20 |                 14 |
|          1 | EarnedLeave |         30 |                 24 |
|          2 | EarnedLeave |         12 |                 10 |
|          3 |   SickLeave |          8 |                  7 |