I have the following data
ACT_NAME
ACT_COMPID
ACT_ADDDATE
ACT_SEQ_ID
ACT_PREV_SEQ_ID
ACT_TYPE
3 Degrees Ministries
3063936
3/5/2010 8:48:20 AM
CC-Audit
3 Degrees Ministries
3063936
2/8/2012 8:08:57 AM
1813572
CC-Audit
3 Degrees Ministries
3063936
3/28/2012 3:55:08 PM
2004469
CC-Audit
3 Degrees Ministries
3063936
3/4/2013 9:45:45 AM
3012859
CC-Audit
3 Degrees Ministries
3063936
3/8/2013 2:58:54 PM
3047315
3012859
CC-Audit
3 Degrees Ministries
3063936
9/5/2013 3:08:33 PM
3872062
CC-Audit
3 Degrees Ministries
3063936
10/21/2013 10:00:52 AM
4064592
3872062
CC-Audit
3 Degrees Ministries
3063936
10/22/2013 10:01:22 AM
4069930
4064592
CC-Audit
3 Degrees Ministries
3063936
10/23/2013 10:01:10 AM
4082301
4069930
CC-Audit
3 Degrees Ministries
3063936
10/24/2013 10:01:05 AM
4090210
4082301
CC-Audit
3 Degrees Ministries
3063936
10/25/2013 2:20:38 PM
4097320
4090210
CC-Audit
3 Degrees Ministries
3063936
10/29/2013 10:07:16 AM
4113446
4097320
CC-Audit
3 Degrees Ministries
3063936
10/30/2013 2:25:06 PM
4123220
4113446
CC-Audit
3 Degrees Ministries
3063936
10/31/2013 2:44:25 PM
4131667
4123220
CC-Audit
I want to retrieve all the yellow highlighted records i.e.
If there’s no ACT_PREV_SEQ_ID the record is considered the start of an “audit event”, and the the final entry that has a ACT_PREV_SEQ_ID is the last action taken in that event.
Any thoughts on how to go about this? I’ve tried using windowing functions but I always get the Max(ACT_ADDDATE) for each event to be the very last record …NOT the last record for that event.