28 Jun 12 12:02
I apologize. Ok so here is why. I need to count the days between when amendments are submitted and when they are approved. My sample data above shows that there could be (multiple) rejections and submissions before an approval is given. Basically, I need to weed those out and only count the original submission and approval for each amendment submitted. An amendment is tied to a projectid, but the amendments themselves are not given a unique number to filter by. So the run down is this - a project id can have more than one amendment and an amendment can be submitted and rejected more than one time before it's approved.
So based on my sample data -
ProjectID date EventComment EventID
1234 2/14/2012 SYSTEM:Submit 30103
1234 2/16/2012 SYSTEM:Reject 30306
1234 2/22/2012 SYSTEM:Submit 30103
1234 2/28/2012 SYSTEM:Approve 30308
1234 3/30/2012 SYSTEM:Submit 30103
1234 4/12/2012 SYSTEM:Approve 30308
ProjectID SubmitDate ApproveDate
1234 2/14/2012 2/28/2012
1234 3/20/2012 4/12/2012
If this formatting is not possible, I'll take it the original way with each row being a date.
I hope that helps to clear it up. I'm not even sure if it can be done. I was thinking of a loop where it would start at the first submission date and end at the approval date and start over if the approval eventid count for the project is greater than 1, but I don't even know where to start there.
I appreciate you taking a look at this.