Selecting a specific value from an audit log

[maxamis4]

Hello folks need some ideas here for a report that I am getting together. Here is the scenerio, currently we have an SLA that states that an external group transferring a problem to the internal group can qualify this problem as First resolve. Normally I would just grab the first row that has external to internal but the groups are nothing alike and there are multiple entries in the problem. Here is an example of my data from my audit table.

 Problem number   TransMeth   Date              before_val    After Val
1009             GROUP      9/1/2009 2:15PM   Beta-Group    Alpha-Group
1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1
1009             GROUP      9/1/2009 3:42PM   INT-Group1    Alpha-Group
1009             GROUP      9/1/2009 3:50PM   Alpha-Group   Beta-Group
1009             GROUP      9/1/2009 4:15PM   Beta-Group    INT-Group2
1009             GROUP      9/1/2009 5:00PM   INT-Group2    Beta-Group
 
 

First let me add some quick notes. My internal groups have a unique identifier in them as shown in the example (INT) being this identifier. What I want to gather from this is how to get the first transfer date where External group (Alpha Beta) goes to Internal group (INT). Ideally here I would use the min (date) function to return the first record. But I need to ensure that this is the first transfer of this nature.


Looking at the example below you see that my internal group goes to my external group. This nullifies my original objective and this record can not be used. But based on my logic above I would still return a value which would be an incorrect value to return. I only need to return values that have the distinct criteria of transferring from external to internal as the first internal entry.

 Problem number   TransMeth   Date              before_val    After Val
1009             GROUP      9/1/200911:01AM  INT-Group1     Beta-Group
1009             GROUP      9/1/2009 2:15PM   Beta-Group    Alpha-Group
1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1
1009             GROUP      9/1/2009 3:42PM   INT-Group1    Alpha-Group
1009             GROUP      9/1/2009 3:50PM   Alpha-Group   Beta-Group
1009             GROUP      9/1/2009 4:15PM   Beta-Group    INT-Group2
1009             GROUP      9/1/2009 5:00PM   INT-Group2    Beta-Group
 
 

I hope this makes since. I was toying with a count statement or a min(date) statement that captures the first date and compares it to the values returned buy I am having no luck and just need to be pointed in the right direction

[ck9663]

Based on your 2 samples, what would be the returned data sets?

--- CK

[maxamis4]

Statement one

1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1

Statement two


I should get no results since the transfer went to a non INT group. The problem is that it came back and in this case it should not be counted. My results are

1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1