sql - Sort by Acct # then delete based on specified text then subtract time variable for each Acct # Code attached -

i needing figure out time takes complete account. first data needs sorted "id" smallest largest "act_updated oldest newest (like excel sort) however, need delete data begins "editing begin" doesnt make change "editing end" directly below "editing begin".

id         act_note     updated_by     act_updated 1651    editing end    name1      may-18-2015 13:05:07 46485   editing end    name4      may-15-2015 16:38:35 111213  comment        name3            may-19-2015 15:34:09 111213  editing end        name3      may-19-2015 15:34:16 111213  editing begin      name3      may-19-2015 15:34:33 111213  comment            name3      may-19-2015 15:35:00 111213  editing end    name3      may-19-2015 15:35:00 556456  editing begin      name2      may-18-2015 12:05:53 556456  editing end    name2      may-18-2015 12:06:00 

in short, hope have data aligned as:

    id  act_note    updated_by  act_updated 111213  editing begin   name3       5/19/2015  3:34:33 pm 111213  comment     name3       5/19/2015  3:35:00 pm 111213  editing end name3       5/19/2015  3:35:00 pm 

the data should remain has following sequence (editing begin -- comment -- editing end). time of comment , editing end same.

i tried using code not correct:

select iif(or(acct_note=1,'editing begin',””,'editing end')(act_updated id    act_note !='editing begin' , act_note !='editing end';  

as can see id 111213 has 2 "editing end" can deleted.

then last step subtract time (act_updated) "editing begin" "editing end" account 111213 total amount of time spend editing. thank time!

select a.*, f.*  from(select act_id     ,act_ref_id     ,act_type     ,act_user_type     ,act_note     ,act_updated_by     ,act_updated_dt  prismmgr.ara_activities)a   left join   (select acct_id     ,acct_no     ,acct_ssn     ,acct_id_lob     ,acct_pop_segment     ,acct_section     ,acct_lob_sys_id     ,acct_created_dt     prismmgr.ara_accounts)f     on a.act_ref_id = f.acct_id    act_type != 'add' , act_type != 'delete' , act_type != 'user entry' , act_updated_dt > '14/may/2015' 

you can order want this:

select id, act_note, update_by, acct_updated, ns.sortorder mytable t left join notesort ns on t.act_note = ns.note order t.id, t.acct_updated, ns.sortorder; 

assuming you've made table this:

create table notesort ( note nvarchar(100) primary key, sortorder int ) ;  insert notesort values ( 'editing begin', 1 ); insert notesort values (  'comment', 2 );  insert notesort values ( 'editing end', 3 );  

but doesn't address harder part of ensuring there's 3 'activities' (begin, comment, end). can select comments , join find begin , end activities. this:

;with x (    -- make derived table sort order of activities   select id, act_note, update_by, acct_updated, ns.noteorder,     row_number() on ( partition id order acct_updated, ns.noteorder )        activitynum   mytable t   left join notesort ns on t.act_note = ns.note )  select x.id, x.act_note, x.update_by, x.acct_updated,       completeactvities.editingtime    x inner join (      -- join 3 activities together, filter out    -- don't occur 1 after other   select x_comment.id        , x_begin.activitynum begin_activitynum        , x_comment.activitynum comment_activitynum        , x_end.activitynum end_activitynum        , cast( ( x_end.acct_updated - x_begin.acct_updated ) time ) editingtime   x x_comment   inner join x x_begin on x_comment.id = x_begin.id                        , x_begin.activitynum = x_comment.activitynum-1                       , x_begin.noteorder = 1   inner join x x_end on x_comment.id = x_end.id                       , x_end.activitynum = x_comment.activitynum+1                       , x_end.noteorder =3   x_comment.noteorder = 2 ) completeactvities      -- join main list, filtering      -- activities complete set       on x.id = completeactvities.id      , x.activitynum in ( completeactvities.begin_activitynum,   completeactvities.comment_activitynum, completeactvities.end_activitynum ) order id, activitynum  

sqlfiddle