select - SQL: Return first instance of an access request -

i use sql , i'm having trouble task. table has information need.

a person can request access sector of building, , 2 types of approbations may needed.

i have discover data first request user did each building has access

if user jon requested access sector "a" , 2 weeks later sector "b", have return information approved sector's "a" , when.

i can find out sector requested first , date. don't know how return approvers since in row. think key here use ticket number.

records

id  eventdate   ticket  user    action  event   approver    building    sector  status 15  7/1/2015 12:25  17c9f862    4003321 new access request  started -   ohio    it_2    running 14  7/1/2015 12:41  17c9f862    4003321 approved    manager approval    4001719 ohio    it_2    running 12  7/1/2015 15:29  17c9f862    4003321 -   finished    -   ohio    it_2    finished: approved 13  7/1/2015 15:29  17c9f862    4003321 approved    director approval   4003468 ohio    it_2    running 10  7/1/2015 20:57  897b9a0a    4003321 new access request  started -   ohio    development running 11  7/1/2015 20:57  f3dcfb96    4003321 new access request  started -   ohio    infra_2 running 9   7/1/2015 20:58  897b9a0a    4003321 approved    manager approval    4001719 ohio    development running 8   7/1/2015 20:58  f3dcfb96    4003321 approved    manager approval    4001719 ohio    infra_2 running 7   7/1/2015 21:01  f3dcfb96    4003321 approved    director approval   4001547 ohio    infra_2 running 6   7/1/2015 21:01  f3dcfb96    4003321 -   finished    -   ohio    infra_2 finished: approved 4   7/1/2015 21:03  897b9a0a    4003321 -   finished    -   ohio    development finished: approved 5   7/1/2015 21:03  897b9a0a    4003321 approved    director approval   4001549 ohio    development running 3   7/1/2015 21:22  3e18483e    4003321 approval not needed finished    -   ohio    it_1    finished: approved 2   7/2/2015 9:48   f902eb9c    4003321 approval not needed finished    -   utah    finance finished: approved 1   7/2/2015 11:08  c186101c    4003321 approval not needed finished    -   ohio    infra_1 finished: approved 

desired result:

user    manager approver    director approver   building    sector  date 4003321 4001719 4003468 ohio    it_2    7/1/2015 15:29 4003321 -   -   utah    finance 7/2/2015 9:48 

sql used

select sel.user      , sel.building      , sel.sector      , sel.eventdate      , sel.ticket  accessrequests sel  inner join           (           select user                , building                , sector                , min(eventdate) data           accessrequests           event = 'finished'             , status = 'finished: approved'             , user != 'null'           group user                 , building                 , sector           order user         ) re on re.data = sel.eventdate              , sel.status = 'finished: approved' 

sorry, couldn't tell sample data 1 column's data ended , next 1 started may have columns wrong, try adding in this:

left join (select      ticket     , approver  accessrequests  event = 'approved  director approval') director_approval  on director_approval.ticket = sel.ticket  left join (select      ticket     , approver  accessrequests  event = 'approved  manager approval') manager_approval  on manager_approval.ticket = sel.ticket 

then in select add add in

isnull(manager_approval.approver,'') manager_approver, isnull(director_approval.approver,'') director_approver 

you may need tack subquery there, should point in right direction :)