Hi everyone,

I'm new to the forum and have what very well may be a very basic question but here goes.  (I work for an animal shelter.)

I am looking to see what animals who were admitted to our shelter on a specific date have NOT yet had a treatment recorded for them after the intake date.

From what I can see I need a statement which in essence says 'not greater than' because there very well may be treatments recorded from an earlier date.  But how do I test to see if a record doesn't exist?

I appreciate your reply.

It is the SQL side of things that trip me up.  Writing up a SQL command statement is where I struggle.  I've been told that Crystal cannot do the 'not exists' side of things without the SQL command being part of the report.

I've tried unsuccessfully to create the SQL code but cannot seem to get the syntax correct.

I tried that and it doesn't seem to work.  It comes up with records which do have a later treatment date and ones which do not have later treatments.

Still no-go - I can't get it to pull the right records.

I've taken a shot at writing some SQL code to do this but haven't been able to get the second 'no exists' syntax correct.  What I have so far is this:
select *
from sysadm.kennel k
inner join sysadm.animal a on k.animal_id=a.animal_id
inner join sysadm.person p on k.owner_id=p.person_id
where k.outcome_type='ADOPTION'

and not exists (
select 'Y' from sysadm.kennel k2
where k.animal_id=k2.animal_id
and k2.intake_date+k2.intake_time>k.outcome_date+k.outcome_time
and k2.intake_type='RETURN')

************
This code selects the animals who have been adopted from he shelter and have not been returned.

However, here is where I get stuck.  How do I code another 'not exists' clause?  I then have to say 'no exists' for any treatments later than the intake date.  I can't seem to get it to work from with CR so SQL is the only way to accomplish it but I just cannot figure out the syntax.

Thanks for your responses.  I appreciate it.  The final SQL code came about as a result of many people's responses.

In case you are interested, here is the final SQL code:
SELECT DISTINCT
 p.person_id,
 p.first_name,
 p.last_name,
 p.street_no,
 p.street_no_suffix,
 p.street_dir,
 p.street_name,
 p.street_type,
 p.quadrant,
 p.apt,
 p.city,
 p.state,
 p.zip_code,
 p.zip_code_suffix,
 p.email_addr,
 p.phone_area_code,
 p.phone_number,
 p.alt_area_code,
 p.alt_phone,
 k.impound_no,
 k.outcome_date,
 a.animal_id,
 a.animal_name,
 a.sex,
 a.age_now,
 a.primary_breed,
 a.secondary_breed,
 a.primary_color,
 a.secondary_color
from
sysadm.kennel k inner join sysadm.animal a on k.animal_id=a.animal_id
  inner join sysadm.person p on k.owner_id=p.person_id
where
k.outcome_type='ADOPTION' and
not exists (select 'Y'
  from sysadm.kennel k2
  where
   k2.animal_id = k.animal_id and
   k2.intake_date >= k.outcome_date and
   k2.source_id = k.owner_id and
   k2.intake_type='RETURN') and
not exists (select 'Y'
  from sysadm.memo m
  where
   m.memo_id = k.animal_id and
   m.memo_date > k.outcome_date and
   m.memo_type = 'FOLLOW UP')