How to fetch the First start date and latest end date from multiple rows in OTBI.

Question

I was trying to fetch the first start date and latest end date for and absence taken. since the short-term disability plan stores multiple rows in weekly wise

so not able to fetch the first start date of the absence and latest end date in a single row. Is there any workaround to achieve this?

Attached screenshot.

Thanks.

Riyaz Ali-Oracle · Answer

Try the below options:

* Use MIN() and MAX() Functions
* Use ROW_NUMBER() for First and Last Entries

You can use ROW_NUMBER() to rank entries and fetch the first and last rows:

sql

WITH AbsenceData AS (

SELECT

absence_start_date,

absence_end_date,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY absence_start_date ASC) AS start_rank,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY absence_end_date DESC) AS end_rank

FROM absence_table

WHERE absence_type = 'Short-Term Disability'

)

SELECT

employee_id,

absence_start_date AS first_start_date,

absence_end_date AS latest_end_date

FROM AbsenceData

WHERE start_rank = 1 OR end_rank = 1;

This ensures you get the first start date and latest end date for each employee.

3. Oracle Fusion Absence Formula Approach

You can use Global Absence Type Duration Formula to retrieve start and end dates dynamically.

https://community.oracle.com/customerconnect/discussion/849745/how-to-get-the-start-date-and-end-date-of-absence-entry-in-global-absence-type-duration-formula

Riyaz Ali-Oracle · Answer

Try the below options:

* Use MIN() and MAX() Functions
* Use ROW_NUMBER() for First and Last Entries

You can use ROW_NUMBER() to rank entries and fetch the first and last rows:

sql

WITH AbsenceData AS (

SELECT

absence_start_date,

absence_end_date,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY absence_start_date ASC) AS start_rank,

ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY absence_end_date DESC) AS end_rank

FROM absence_table

WHERE absence_type = 'Short-Term Disability'

)

SELECT

employee_id,

absence_start_date AS first_start_date,

absence_end_date AS latest_end_date

FROM AbsenceData

WHERE start_rank = 1 OR end_rank = 1;

This ensures you get the first start date and latest end date for each employee.

3. Oracle Fusion Absence Formula Approach

You can use Global Absence Type Duration Formula to retrieve start and end dates dynamically.

https://community.oracle.com/customerconnect/discussion/849745/how-to-get-the-start-date-and-end-date-of-absence-entry-in-global-absence-type-duration-formula

MohammedAmeenmfp10 · Answer

Hi Bhaskar Konar

yes i tried using the aggregate function like MIN/MAX but it did not work as expected. still the data are in multiple rows.

Thanks.