July 31, 2018

Find leave taken in alternate intervals within a month

We saw the Rank Function in my previous post for sorting records. In the same way using the function we can get the count of leaves taken in alternate intervals with a month for an employee.

For example:
Leaves taken on 1,2,3,4 of a month and came to office on 5th and again went on leave from 6th. the summary of absence using the query can be obtained.

select min(date_taken) date_from,
       max(date_taken) date_to,
       count(*) num_days
from (
  select date_taken,
         date_taken-row_number() over(order by date_taken) as daysto
         from Absence
     )
group by daysto
order by 1
DATE_FROMDATE_TONUM_SAMPLES
01-DEC-1504-DEC-154
07-DEC-1510-DEC-154
14-DEC-1516-DEC-153
19-DEC-1520-DEC-152


No comments:

Post a Comment

Thanks for your comments submitted.,will review and Post soon! by admin.

Part of my Oracle Fusion HCM Fix of the Day series: real issues from implementations and a Redwood rollout I led without a Systems Integrat...