Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to return data from another record based on a table lookup 1

Status
Not open for further replies.

aks12

Technical User
Nov 10, 2005
60
US
Hello, I'm using Crystal XI and need some outside advice. I have the following two tables:

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
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
Code:
[u]BeginEvent[/u] [u]EndEvent[/u]
1          18
1          -9
1           2
1           5
14          3
Output should look like:
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
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?
 
This would almost have to be done on the database side, using a stored procedure or command object. If that's a possibility for you then provide your database type and version and I can give you a good head start in that direction.

 
I'm using Oracle 10g. I don't have any write access to the database, so a command object would be great.
 
I think you could handle this by using a left outer join FROM Claims.EventID TO EventPairs.BeginEvent and then add the Claims table again (it will appear as Claims_1) and link EventPairs.EndEvent to Claims_1.EventID using a left outer join again (From EventPairs to Claims_1). Then create a formula for {@EventEndDate}:

if {EventPairs.BeginEvent} = {Claims.EventID} and
not isnull({EventPairs.EndEvent}) and
{EventPairs.EndEvent} = {Claims_1.EventID} then
{Claims_1.EventDate}

-LB
 
lbass, your solution is close, but it's giving me extra rows and assigning event end dates to claim# where it shouldn't. Here's the output I get when I set up the join and formula you suggested:
Code:
Claim#  EventID Event_Begin_Date  Event_End_Date
00123	1	  4/22/10 1:23pm     3/31/10 9:26am
00123	1	  4/22/10 1:23pm     4/22/10 1:24pm
00123	1	  4/22/10 1:23pm	
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     4/22/10 1:25pm
00299	14	 4/1/10 10:02am     4/1/10 11:54am
00299	3	  4/1/10 11:54am
 
Change the formula to:

if {EventPairs.BeginEvent} = {Claims.EventID} and
not isnull({EventPairs.EndEvent}) and
{EventPairs.EndEvent} = {Claims_1.EventID} and
[red]{Claims.Claim#} = {Claims_1.Claim#}[/red] then
{Claims_1.EventDate}

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top