Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Counting number of rows up to a specified value by an id

Received Response
4
Views
1
Comments
Max503
Max503 Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

Hello,

I have a dataset with id, test date, test_type, test result.

ID Test_Date Test_Type Test_Result

1 2024-03-21 A Fail

1 2024-04-21 A Fail

1 2024-04-30 A Pass

1 2025-05-15 B Fail

1 2025-05-31 B Pass

I need to create a variable counting number of attempts to pass for each Test_Type. So the desired data would be:

ID Test_Date Test_Type Test_Result Attempts

1 2024-03-21 A Fail 3

1 2024-04-21 A Fail 3

1 2024-04-30 A Pass 3

1 2025-05-15 B Fail 2

1 2025-05-31 B Pass 2

Could someone help with a SQL code?

Thanks in advance!

Max

Answers

  • Riyaz Ali-Oracle
    Riyaz Ali-Oracle Rank 6 - Analytics Lead

    Hi @User_KNFCO ,

    Please try the below:

    SELECT
    ID,
    Test_Date,
    Test_Type,
    Test_Result,
    COUNT(*) OVER (
    PARTITION BY ID, Test_Type
    ORDER BY Test_Date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Attempts
    FROM (
    SELECT *,
    MIN(CASE WHEN Test_Result = 'Pass' THEN Test_Date END)
    OVER (PARTITION BY ID, Test_Type) AS First_Pass_Date
    FROM your_table
    ) sub
    WHERE Test_Date <= First_Pass_Date
    ORDER BY ID, Test_Type, Test_Date;

    Thanks,
    Riyaz Ali