I am needing to figure out the time it takes someone to complete an account. First the data needs to be sorted by "ID" smallest to largest then by "ACT_UPDATED oldest to newest (like an excel sort) However, I need to delete data where someone begins "Editing Begin" but doesnt make a change thus "Editing End" is 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, I hope to have the 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 only data should remain that has following sequence (Editing Begin -- Comment -- Editing End). Sometimes the time of the Comment and Editing End are the same.
I tried using this code but its not correct:
SELECT IIF(OR(ACCT_NOTE=1,'Editing Begin',””,'Editing End')(ACT_UPDATED from ID
where ACT_NOTE !='Editing Begin' and ACT_NOTE !='Editing End'; As you can see ID 111213 has two "Editing End" which can be deleted.
Then the last step is to subtract the time (ACT_UPDATED) "Editing Begin" from the "Editing End" for account 111213 to get the total amount of time spend editing. Thank you for your time!
Select a.*,
f.*
from(select ACT_ID
,ACT_REF_ID
,ACT_TYPE
,ACT_USER_TYPE
,ACT_NOTE
,ACT_UPDATED_BY
,ACT_UPDATED_DT
from 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
from PRISMMGR.ARA_ACCOUNTS)f
on a.ACT_REF_ID = f.ACCT_ID
where ACT_TYPE != 'ADD' and ACT_TYPE != 'DELETE' and ACT_TYPE != 'USER ENTRY' and ACT_UPDATED_DT > '14/MAY/2015'
You can get the order you want with this:
select ID, ACT_NOTE, UPDATE_BY, ACCT_UPDATED, ns.sortOrder
from myTable t
left join NoteSort ns on t.ACT_NOTE = ns.note
order by t.ID, t.ACCT_UPDATED, ns.sortOrder;
assuming you've made a table like this:
create table NoteSort
( note nvarchar(100) primary key, sortOrder int ) ;
insert into NoteSort values ( 'Editing Begin', 1 );
insert into NoteSort values ( 'Comment', 2 );
insert into NoteSort values ( 'Editing End', 3 );
But that doesn't address the harder part of ensuring there's all three 'activities' (begin, comment, end). For that you can just select the Comments and join to itself to find the Begin and End activities. Like this:
;with x as (
-- make a derived table with the sort order of activities
select ID, ACT_NOTE, UPDATE_BY, ACCT_UPDATED, ns.noteOrder,
row_number() over ( partition by ID order by acct_Updated, ns.noteOrder )
as activityNum
from 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
from x
inner join (
-- join the three activities together, which will filter out any
-- that don't occur one after the other
select x_comment.id
, x_begin.activityNum as begin_ActivityNum
, x_comment.activityNum as comment_ActivityNum
, x_end.activityNum as end_ActivityNum
, cast( ( x_end.acct_updated - x_begin.acct_updated ) as time ) as EditingTime
from x x_comment
inner join x x_begin on x_comment.id = x_begin.id
and x_begin.activityNum = x_comment.activityNum-1
and x_begin.noteOrder = 1
inner join x x_end on x_comment.id = x_end.id
and x_end.activityNum = x_comment.activityNum+1
and x_end.noteOrder =3
where x_comment.NoteOrder = 2
) as completeActvities
-- join back to the main list, filtering for only
-- the activities with the complete set
on x.id = completeActvities.id
and x.activityNum in ( completeActvities.begin_activityNum, completeActvities.comment_activityNum, completeActvities.end_activityNum )
order by id, activityNum