top of page
Jul 19, 2020

SQL query to get sum total leave duration for particular leave type


Below query will give sum of all duration for particular leave applied for certain period.


There are four parameters used to make below query generic -

  • P_NAME : This is the name of the Absence Type for which sum of duration is required

  • P_PERSON_ID : Person Id for which the sume duration is required.

  • P_START_DATE : Start Date of the period for which sum duration is required, generally it will be 1st Jan of current Year.

  • P_END_DATE : End Date of the period for which sum duration is required, generally it will be 31st Dec of current Year.

select SUM(A.DURATION)
from ANC_PER_ABS_ENTRIES A, ANC_PER_ABS_ENTRIES B  where B.ABSENCE_TYPE_ID=(SELECT ABSENCE_TYPE_ID FROM ANC_ABSENCE_TYPES_VL WHERE NAME = :{PARAMETER.P_NAME}) 
AND B.per_absence_entry_id=A.per_absence_entry_id AND B.absence_status_cd='SUBMITTED' and B.approval_status_cd='APPROVED'
AND A.start_DATE >= :{PARAMETER.P_START_DATE}  and A.start_DATE <= :{PARAMETER.P_END_DATE}
AND A.DURATION <> 0
AND B.PERSON_ID = :{PARAMETER.P_PERSON_ID}

This query comes very handy in multiple scenarios where we need to enforce validation and restriction considering different business rules.

Comments

bottom of page