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

MohammedAmeenmfp10

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.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24556/how-to-fetch-the-first-start-date-and-latest-end-date-from-multiple-rows-in-otbi

Hi @MohammedAmeenmfp10,

Have you tried using some aggregate functions like MIN / MAX etc using with those dates?

Thank You!

MohammedAmeenmfp10

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.

Riyaz Ali-Oracle

Try the below options:

1. Use MIN() and MAX() Functions
2. 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

Try the below options:

1. Use MIN() and MAX() Functions
2. 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