SubQuery in CR 1

[indoaryaan]

I have to use the following query with subquery in CR6. I know i cannot use subqueries in CR. What would be the work around ?? The query below works in Crystal Query Designer.

SELECT
PERSONNEL."PERSONNELNO", PERSONNEL."PERSONNELNAME"
FROM
"SYSADM"."PERSONNEL" PERSONNEL
WHERE
(PERSONNEL."PERSONNELNAME" <> 'ABC' AND
PERSONNEL."PERSONNELNAME" <> 'ASDF' AND
PERSONNEL."PERSONNELNAME" <> 'XYZ' ) AND
PERSONNEL."PERSONNELNO" NOT IN
( SELECT DISTINCT TIMEACCOUNTING."PERSONNELNO"
FROM "SYSADM"."TIMEACCOUNTING" TIMEACCOUTNING
WHERE TIME."DATE" >= ? AND
TIME."DATE" <= ?)
ORDER BY
PERSONNEL."PERSONNELNAME" ASC


Notes :

1. The Personnel table has columns “Personnelno”-(PK), “Personnelname” and Time table has columns “Personnelno” ref. Personnel.Personnelno and “Date”.
2. Date( BeginDate and EndDate have to be passed as parameters ).

The above query performs in Crystal Query Designer. I am not able to reproduce it in Select Expert or Record Selection Formula Editor. Is there a work around. Database is an ODBC source.

Thanks in advance…..

 

[lbass]

Try using a record select statement of:

not({PERSONNEL.PERSONNELNAME} in ['ABC','ASDF','XYZ'])

Group on {Personnel.PersonnelNo} and then create a formula {@inperiod}:

if {time.date} in {?BeginDate} to {?EndDate} then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@inperiod},{Personnel.PersonnelNo}) = 0

This would select only those employees with no dates within the parameter period.

-LB