test

[aks12]

Hello, I'm using Crystal XI and need some outside advice. I have the following two tables:
Claims

[u]Claim#[/u]  [u]Event_ID[/u]   [u]Event_Date[/u]
00123        1     4/22/10 1:23pm
00123        2     4/22/10 1:24pm
00123        3     4/22/10 1:25pm
00299        5     3/31/10 9:26am
00299        14    4/1/10 10:02am
00299        3     4/1/10 11:54am

Each Event_ID from the Claims table can be either the beginning or ending event of a particular claim# as seen in the following table:

Event_Pairs

[u]Begin_Event[/u] [u]End_Event[/u]
1           18
1           -9
1            2
1            5
14           3

Output should look like:

[u]Claim#[/u]  [u]Event_ID[/u]  [u]Event_Begin_Date[/u]   [u]Event_End_Date[/u] 
00123        1    4/22/10 1:23pm     4/22/10 1:24pm
00123        2    4/22/10 1:24pm  
00123        3    4/22/10 1:25pm  
00299        5    3/31/10 9:26am
00299        14   4/1/10 10:02am     4/1/10 11:54am
00299        3    4/1/10 11:54am

In other words, within each unique Claim#, I want to look up its Event_ID from the Event_Pairs table and return the Event_Date if a matching pair was found. In the example output above, the first line would show an Event_End date of 4/22/10 1:24pm because Event_IDs 1 and 2 are a valid event pair. Likewise, in Claim# 00299, the Event_End_Date for Event_ID 14 is 4/1/10 11:54am because 14 and 3 are a valid event pair. If an Event_ID from the Claims table is only an End_Event or does not exist in the Event_Pairs table, then its Event_End_Date should be left blank in the output. What would be the best way to accomplish this?

 

[aks12]

Please ignore this post, I need to put in a better thread title.