Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to fetch the First start date and latest end date from multiple rows in OTBI.
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.
Answers
-
Have you tried using some aggregate functions like MIN / MAX etc using with those dates?
Thank You!
0 -
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.
0 -
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
0 -
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
0
