0

This is my logs table:

Job_id Event. Event_time.
1001 job_actioned 1:00
1001 job_approved 1:20
1001 job_actioned 1:45
1001 job_approved 2:22
1001 job_actioned 3:40
1001 job_approved 3:51

I would like to match the approval to the previous job so that I can calculate the time it took for each job to be approved like this:

Job_id Event. Event_time. Event2. Event_time2. approve_time
1001 job_actioned 1:00 job_approved 1:20 0:20
1001 job_actioned 1:45 job_approved 2:22 0:37
1001 job_actioned 3:40 job_approved 3:51 0:11

how would I do this on SQL?

I tried LEFT joining each actioned item with each job approved item using a left join then picking the one with the MIN approve time that's greater than 0. This worked but my table has over 1 million rows and I'm not too sure that's the most efficient method.

nicholas kanyagia
  • 11
  • 1

1 Answers1

0

use rownumber function and make row number after you can join table and getting your result like this

SELECT [t1.Job_id],t1.[EVENT.],t1.[Event_time.],
t2.[EVENT.] as [Event2.],t2.[Event_time.] as [Event_time2.],
DATEDIFF(MINUTE,t1.[Event_time.],t2.[Event_time.]) as [approve_time]
FROM (
    SELECT *,ROW_NUMBER() OVER(ORDER BY Job_id) AS RNO FROM logs
    WHERE [EVENT.] = 'job_approved'
)t1 
LEFT JOIN (
    SELECT *,ROW_NUMBER() OVER(ORDER BY Job_id) AS RNO FROM logs
    WHERE [EVENT.] = 'job_actioned'
)t2 ON t1.RNO = t2.RNO
Piyush Kachhadiya
  • 617
  • 8
  • 18