Hello, I'm using Crystal XI and need some outside advice. I have the following two tables:
Claims
Each EventID from the Claims table can be either the beginning or ending event of a particular claim# as seen in the following table:
Event_Pairs
Output should look like:
In other words, within each unique Claim#, I want to look up its EventID from the Event_Pairs table and return the EventDate 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 EventIDs 1 and 2 are a valid event pair. Likewise, in Claim# 00299, the Event_End_Date for EventID 14 is 4/1/10 11:54am because 14 and 3 are a valid event pair. If an EventID from the Claims table is only an EndEvent 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?
Claims
Code:
[u]Claim#[/u] [u]EventID[/u] [u]EventDate[/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
Event_Pairs
Code:
[u]BeginEvent[/u] [u]EndEvent[/u]
1 18
1 -9
1 2
1 5
14 3
Code:
[u]Claim#[/u] [u]EventID[/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
What would be the best way to accomplish this?