Hi,
I've the below table structure with records as sampled below. iam trying to compute the FINAL_SCORE as stated in step #5 but iam trying to limit the nesting levels as much as possible.
CREATE TABLE TXGOV.Violation_Tasks b
(
serial_number number primary key,
Violation_id number not null,
Violation_Name varchar2(100) ,
category_id number not null,
score_percent number not null,
start_date date,
end_date date
);
SELECT * FROM TXGOV.Violation_Tasks b
serial_number, Violation_id , Violation_Name, category_id , score_percent, start_date, end_date
-----------------------------------------------------------------------------------------------------------
1 , 23 Model 11 0 01/11/2024, 03/04/2024
2 , 70 State 11 89 01/09/2024, 05/19/2024
3, 23 Market 9 99 11/11/2024, 12/10/2024
5 , 11 Atlantic 5 100 09/08/2022, 11/19/2022
7 , 70 City 11 77 01/13/2020, 01/30/2020
11, 79 Model 45 45 01/02/2023, 01/17/2023
20, 79 Atlantic 11 5.9 01/06/2023, 09/27/2023
or,
Scenario/steps to get Final score value:
1. CountOfRows1:
get the count of records grouped by category_id with at least one record WHERE Violation_Name='Atlantic'
if #1 found get the count of all records grouped by category_id regardless of Violation_Name(any name) ---> this becomes "CountOfRows1"
2. STARTDATE_ACTIVITY:
if "CountOfRows1" = 2 (from #1 above)
then take maximum of start_date grouped by category_id
else
take the minimum of start_date grouped by category_id
3. ENDDATE_ACTIVITY:
take the minimum of end_date where start_date=STARTDATE_ACTIVITY
4. COMPLETION_RATE:
Take the maximum of score_percent where start_date=STARTDATE_ACTIVITY and end_date=ENDDATE_ACTIVITY
5. FINAL_SCORE:
take the minimum where
start_date=STARTDATE_ACTIVITY and end_date=ENDDATE_ACTIVITY and score_percent=COMPLETION_RATE
Any suggestion on how to handle this requirement is appreciated.
using oracle 12.2c.
thanks,