By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Selecting a specific value from an audit log

maxamis4
Expert 100+
P: 295
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.

Expand|Select|Wrap|Line Numbers
  1. Problem number   TransMeth   Date              before_val    After Val
  2. 1009             GROUP      9/1/2009 2:15PM   Beta-Group    Alpha-Group
  3. 1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1
  4. 1009             GROUP      9/1/2009 3:42PM   INT-Group1    Alpha-Group
  5. 1009             GROUP      9/1/2009 3:50PM   Alpha-Group   Beta-Group
  6. 1009             GROUP      9/1/2009 4:15PM   Beta-Group    INT-Group2
  7. 1009             GROUP      9/1/2009 5:00PM   INT-Group2    Beta-Group
  8.  
  9.  
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.

Expand|Select|Wrap|Line Numbers
  1. Problem number   TransMeth   Date              before_val    After Val
  2. 1009             GROUP      9/1/200911:01AM  INT-Group1     Beta-Group
  3. 1009             GROUP      9/1/2009 2:15PM   Beta-Group    Alpha-Group
  4. 1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1
  5. 1009             GROUP      9/1/2009 3:42PM   INT-Group1    Alpha-Group
  6. 1009             GROUP      9/1/2009 3:50PM   Alpha-Group   Beta-Group
  7. 1009             GROUP      9/1/2009 4:15PM   Beta-Group    INT-Group2
  8. 1009             GROUP      9/1/2009 5:00PM   INT-Group2    Beta-Group
  9.  
  10.  
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
Dec 23 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Based on your 2 samples, what would be the returned data sets?

--- CK
Dec 23 '09 #2

maxamis4
Expert 100+
P: 295
Statement one

Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 1009             GROUP      9/1/2009 2:45PM   Alpha-Group   INT-Group1
  2.  
Dec 24 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.