Oracle SQL- Apply a "Unique ID" Column, Based on Other Column Values

Question

I have a query that pulls multiple properties and their daily details in and I'm needing a way to identify all the failures uniquely within each property. This is an edit to the post, but wanted to note that the dates do come in a unique timestamp form, so there shouldn't be any overlapping. I also added a Row_Number column partitioned by Property and Date/Timestamp, per recommendation.

Example Table

Failures are one-line rows that are defined by the "Failure Flag" column as a value= 'Y', along with a Failure Type. The failure isn't completely over until the activity log ends the "Trouble Flag" column, marked by value= 'Y'. Trouble Type will also indicate what issues are happening. The goal is to create a "Failure ID" column that will apply a group ID to the Failure and it's associated Trouble Flagged rows.

I've tried to do a basic kind of count approach, but this didn't work the way I was hoping... thinking this may be more of a DenseRank opportunity here?

case
  when Failure_flag= 'Y' and Trouble_Type is not null then count(Trouble_Type) over (partition by Prprty order by Date)
  else null
  end as Failure_count

Important things to note: There are multiple Failure Types and Trouble Types and there can be multiple failures of the same Failure Types and Trouble Types within the same day (or on a later date) or property!

Hopefully this situation makes sense... please let me know if further clarification is needed! Appreciate any advice or help offered!

Answer 1

If your date col (which cannot actually be named "date" as that's a reserved keyword) is a date with time in it or a timestamp and is unique, always increasing in the order of the line depicted in your image, and there is no overlapping failures, then you can use that to identify each failure using two sets of analytical functions, one of which uses the IGNORE NULLS option in combination with DECODE (to mark unwanted rows with a NULL) to find that failure flag row and return its timestamp value, which is then later used to do the ranking. Something like this demonstrates the technique:

/* testdata */ WITH data AS (SELECT SYSDATE mydate, NULL failure_flag FROM dual
              UNION ALL
              SELECT SYSDATE + 0.05 mydate, NULL failure_flag FROM dual
              UNION ALL
              SELECT SYSDATE + 0.1 mydate, 'Y' failure_flag FROM dual
              UNION ALL
              SELECT SYSDATE + 0.3 mydate, NULL failure_flag FROM dual
              UNION ALL
              SELECT SYSDATE + 0.4 mydate, 'Y' failure_flag FROM dual)
SELECT x.*,
       DENSE_RANK() OVER (ORDER BY flag_timestamp) failure_id
  FROM (SELECT x.*, 
               FIRST_VALUE(DECODE(failure_flag,'Y',mydate) IGNORE NULLS) OVER (ORDER BY mydate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) flag_timestamp       
          FROM data x) x
 ORDER BY mydate          

Results: