CODEWITHSUNDEEP
sqloracle-database
LeoEY
LeoEY

Reputation: 29

Oracle SQL Look through column, if condition fulfill, compare dates and insert value to column

need help with oracle sql code. I want my code able to do something like this: Look for Event:TS-0068 then take the Task Number value '55', then look for next '55' with 'EVENT:BC-0050' and compare both date.If Event:TS-0068,date A smaller than Event:BC-0050,Date B then return string "Overhead" at column Condition, else Null or blank will do. I include test data here in link Dummy Data

enter image description here

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Your conditions translate directly into analytic functions and case expressions:

select t.*,
       (case when text2 = 'EVENT:TS-0068' and TASK_NUMBER = 55 and
                  row_number() over (partition by text2, task_number order by starttime) = 1 and
                  min(case when text2 = 'EVENT:BC-0050' then starttime end) over (order by starttime desc) > starttime
             then 'Overhead'
        end) as condition
from t
order by starttime;

Here is a db<>fiddle.

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

Determine the first record with text2='EVENT:TS-0068' when ordered by the start time through use of dense_rank() analytic function in the first query.

Then take only the records with returned dense_rank values equal to 1 in order to create condition column by use of a correlated subquery to scan whether there exists at least one record for text2='EVENT:BC-0050' exceeding starttime value those are for text2='EVENT:TS-0068' : 

WITH t AS
(
 SELECT nvl(case 
            when text2 = 'EVENT:TS-0068' then
                 dense_rank() over (partition by text2 order by starttime)
            end, 0) as dr,
        t.*
   FROM tab t
  ORDER BY starttime
)
 SELECT starttime, text2, task_number,
        case 
        when dr = 1 then
           ( select nvl2( max(starttime), 'Overhead', null )
               from t t1
              where t.starttime < t1.starttime 
                and text2 = 'EVENT:BC-0050')
        end as condition
   FROM t
  ORDER BY starttime;

Demo

Upvotes: 0

Related Questions