Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Max effect date two related records in same table Post Reply Post New Topic
Author Message
John2Chr
Newbie
Newbie
Avatar

Joined: 11 Apr 2012
Online Status: Offline
Posts: 10
Quote John2Chr Replybullet Topic: Max effect date two related records in same table
     Posted: 11 Apr 2012 at 7:17am
I thought of using this table, that has all the info that I want, and linking to itself using the program as a parameter and linking by the Model to get the activity.  In addition, need to apply the latest effective date to the activity and program.  Not sure if it is useful but the first five digits of the program is always the first five of the activity. 
 
Model ATTB ATTB Value Eff Dt.
1234567 Activity 1212145 1/4/2001
1234567 Activity 1234675 2/28/2012
1234567 Activity 1212463 1/1/2001
1234567 Program 12121 7/1/2001
1234567 Program 12121 1/1/2001
1234567 Program 12346 2/4/2001
 
 
DesiredResults:
Model ATTB ATTB Value Eff Dt.
1234567 Activity 1234675 2/28/2012
1234567 Program 12346 2/4/2001
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 11 Apr 2012 at 8:14am
I don't know if CR will allow a self join
 
If it does, I would think that if you used groups you would get the desired result.
 
If it doesn't, and even if it does, I would use a stored proc to select my data:
a) I know I can do the self join
b) much more flexible in the join criteria
c) can ensure that the data returned is the data that I want
 
HTH
IP IP Logged
John2Chr
Newbie
Newbie
Avatar

Joined: 11 Apr 2012
Online Status: Offline
Posts: 10
Quote John2Chr Replybullet Posted: 11 Apr 2012 at 8:54am

I tried the self-join but it didn’t work too well.  I got the alias error and it was not possible to write the max effective date on the alias table.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 11 Apr 2012 at 9:29am
stored proc is going to be a much more reliable method...and you only return the columns that you want...then the report is a piece of cake
IP IP Logged
John2Chr
Newbie
Newbie
Avatar

Joined: 11 Apr 2012
Online Status: Offline
Posts: 10
Quote John2Chr Replybullet Posted: 11 Apr 2012 at 2:07pm
Are you talking about putting the stored proc in a formula/SQL Expression Field?  What might the formula look like?  I have about 10,000 models that I want to grab by Program so program will be the parameter.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 12 Apr 2012 at 5:27am
no, you would call the stored proc to select your data...  the proc would customize the data so that the report has exactly what you want and doesn't have duplicates, etc that you need to deal with in the report.
regardless, the self join is simple in a stored proc
you can't call a stored proc from a formula.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.