How to fetch data from first row to a particular row

I have two tables table 1 (log_audit table) table 2 is (final_log table)

table 1 data looks like below

table 2 data looks like below

Here user “D” actual resolver of the customer ticket. so we want the data from ticket start time (i.e 2020-05-01 10:00:00) to till first assigned to the user “D” (here the ticket first assigned to “D” on “2020-05-01 10:20:00”)

The expected output should be like from 1st row to till 4th row

please help me on this.

Answer

You can use a left join and window functions:

select la.*
from (select la.*,
             min(case when fl.actual_user is not null then la.start_time end) over (partition by la.ticket_id) as actual_user_start_time
      from log_audit la left join
           final_log fl
           on fl.actual_user = t.user
     ) la
where start_time <= actual_user_start_time;