Retrieve records less or equal to a certain date 1

[kernal]

I need to select records less or equal to a certain date but only if the person has at least 1 record with the date that I enter in the parameter and the other events they are attending aren't over. I'm using crystal 9 and getting the data from a mysql database.

PERSON_TABLE

id name
1 John Doe
2 Jane Doe
3 Jimmy Doe

CONFIRM_TABLE
id event_id entered event_date
1 1 2009-06-15 2009-07-05
1 2 2008-01-15 2008-02-15
1 6 2009-04-20 2009-06-29

2 1 2009-05-15 2009-07-05
2 5 2007-02-16 2006-03-15

3 4 2009-06-15 2009-08-16

The parameter is for the {CONFIRM_TABLE.entered field) so:

RESULTS wanted if I enter 2009-06-15 in the parameter:

id name event_id event_date
1 John Doe 1 2009-07-05
1 John Doe 6 2009-06-29 (event 2 is excluded because the event ended on 2008-02-15)

3 Jimmy Doe 4 2009-08-16

======

2 Jane Doe is excluded because there are no records entered on 2009-06-15

Thanks for you help.

 

[lbass]

Create a formula {@hasdate} like this:

if {table.entered} = {?parmdate} then 1

Go to report->selection criteria->record and enter:

{table.entered} <= {?parmdate} and
{table.eventdate} >= {?parmdate}

Then insert a group on {person.id} and go to report->selection formula->GROUP and enter:

sum({@hasdate},{person.id}) > 0

-LB

 

[kernal]

Thanks lbass. Solution worked great. You are the best. Now, they tell me the user wants another table (WAITLIST_table which has the same fields as the CONFIRM_table) included in the report and here is the problem.

A person:
1. Will always be in the PERSON_table but
1. Could be in the CONFIRM_table and WAITLIST_table or
2. Just in the CONFIRM_table or
3. Just in the WAITLIST_table
with the entered date = parameter and other events that aren't over

So the user now wants events in CONFIRM_table and\or WAITLIST_table so:

Example:

CONFIRM_TABLE
id event_id entered event_date
1 1 2009-06-15 2009-07-05
1 2 2008-01-15 2008-02-15
1 6 2009-04-20 2009-06-29

2 1 2009-05-15 2009-07-05
2 5 2007-02-16 2006-03-15

3 4 2009-06-15 2009-08-16

WAITLIST_TABLE
id event_id entered event_date
1 7 2009-06-15 2009-07-10

2 8 2009-06-15 2009-09-10
2 2 2008-01-15 2008-02-15

4 1 2009-06-15 2009-07-05
4 3 2009-04-15 2009-04-16
4 9 2009-06-11 2009-06-22

===
I don't know if I'm even doing the joins and groups correctly now.

I have {PERSON_table.id field} left outer joined to {CONFIRM_table.id field} and {WAITLIST_table.id field}.

===
1st group is {PERSON_table.id field}
2nd group is {CONFIRM_table.event_id field}
3rd group is {WAITLIST_table.event_id field}

Results wanted

RESULTS wanted if I enter 2009-06-15 in the parameter:

=================

JOHN DOE

I need to have a header: CONFIRM before the CONFIRM_table data appears on the report so

CONFIRM

event_id event_date
1 2009-07-05
6 2009-06-29 (event 2 is excluded because the event ended on 2008-02-15)

He also was entered on the WAITLIST_table on 2009-06-15 so I need to have a WAITLIST header before the WAITLIST_table data appears on the report

WAITLIST

event_id event_date
7 2009-07-10

=================

JANE DOE should now be retrieved on the report since she is on the WAITLIST_table with an entered date of 2009-06-15.

WAITLIST

event_id event_date
8 2009-09-10 (event 2 is excluded because the event ended on 2008-02-15)

=================

JIMMY DOE is just on the CONFIRM_table with entered date of 2009-06-15

CONFIRM

event_id event_date
4 2009-08-16

=================

A new person on the report since this person isn't in the CONFIRM_table, just in the WAITLIST_table with an entered date of 2009-06-15

TAMMY DOE (person id is 4)

WAITLIST

event_id event_date
1 2009-07-05
9 2009-06-22 (event 3 is excluded since it ended on 2009-04-16)

=================

Sorry that I didn't know that they wanted this other table included until I showed them the results from your great solution. I hope you can still help me.

 

[lbass]

I would just add the wait list table in a subreport that is linked by the person ID. Place the sub in the person ID group footer section and create the same kind of formulas within the sub. In addition to linking on the person ID, link the parameter from the main report to the parameter you have created in the subreport, by using the dropdown in the lower left of the subreport linking screen to select {?parmdate}, not the default {?pm-?parmdate}.

-LB

 

[kernal]

Lbass, Sorry I haven't had time to reply sooner. I made the waitlist report a subreport, used the same formulas in your previous reply and did all the linking per your newest reply but there is a problem.

TAMMY DOE is not being retrieved. The only id just in the WAITLIST_table with a entered date of 2009-06-15.

IDs in both CONFIRM_table and WAITLIST_table or just CONFIRM_table are being retrieved with the entered date of 2009-06-15.

Thanks

 

[lbass]

Then you will need to use the person table in a container report, where you insert a group on personID and insert a subreport for confirmed and a subreport for waitlist in separate group sections. Create all the parameters in the main report and use for linking to each sub as before.

Another approach would be to use a command as your datasource where you use a union all to bring in all appropriate records.

-LB

 

[kernal]

Thanks. I see which one works.

 

[kernal]

thanks lbass. the command as the datasource worked great.