Select records not present.... 1

[MichaelNZQA]

All

I wish to write a Crystal Report that will return Staff records that have not enetered a transaction between two given dates. If I was to write it in SQL it may look like;
=======================================================
select full_name from staff
where staff_ref not in
(select staff_ref from worktrans
where tran_date > 'Sep 2 2003 12:00AM'
and tran_date < 'Sep 15 2003 12:00AM' )
=======================================================

I would also like to make the TRAN_DATE parameter driven. I was succesful with a complete left-joined listing, that grouped all transaction values then surpressed other than 0. But it failed when I entered the Date Parameters.

Any thoughts?

Regards

Michael.

 

[Frank49001]

I believe it is a matter of Record Selection. You can also the select wizard to replicate this SQL.

Frank

 

[dgillz]

Have you created a date range parameter? If you go to report->edit selection formula->record and enter the following formula:

{Tran_date} in {?DateRangeParm}



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

I would approach this by using a left join from the staff table to the worktrans table. However, do not use a date select on worktrans, since this will, in essence, make your left join an equal join. Instead, create a formula {@workinrange} and place it in the details section:

if isnull({worktrans.date}) or
not({worktrans.date} in {?startdate} to {?enddate}) then
0 else 1

Then group on {staff.staff_ref} and go to report->edit selection formula->GROUP and enter:

sum({@workinrange},{staff.staff_ref}) = 0

This should give you a list of staff who did not work during the specified date range.

-LB

 

[MichaelNZQA]

lbass

This was indeed a top answer. Worked just perfectly.

Regards

MichaelNZQA.