Hi all,

Just wondering if anyone is still using this report and finds that it returns clients with deceased pets.  These pets are not necessarily inactive, they may have been marked as deceased but not inactive..

http://www.openvpms.org/customisation/customer-marketing-export-report

I was attempting to tweak the SQL statement to get it to excluded deceased pets but no luck this is the original sql.

  select
  c.entity_id as id,
  c1.value as lastname,
  c2.value as firstname,
  l3.name as title,
  cd1.value as address,
  l2.name as suburb,
  cd3.value as postcode,
  cd4.value as state,
  ced1.value as email
from entities c
  left outer join entity_details c1 on c1.entity_id = c.entity_id and c1.name = "lastName"
  left outer join entity_details c2 on c2.entity_id = c.entity_id and c2.name = "firstName"
  left outer join entity_details c3 on c3.entity_id = c.entity_id and c3.name = "title"
  left outer join lookups l3 on l3.code = c3.value and l3.arch_short_name = "lookup.personTitle"
  left outer join contacts cc on cc.party_id = c.entity_id and cc.arch_short_name = "contact.location"
  left outer join contact_details cd1 on cd1.contact_id = cc.contact_id and cd1.name = "address"
  left outer join contact_details cd2 on cd2.contact_id = cc.contact_id and cd2.name = "suburb"
  left outer join lookups l2 on cd2.value = l2.code and l2.arch_short_name = "lookup.suburb"
  left outer join contact_details cd3 on cd3.contact_id = cc.contact_id and cd3.name = "postcode"
  left outer join contact_details cd4 on cd4.contact_id = cc.contact_id and cd4.name = "state"
  left outer join contacts ce on ce.party_id = c.entity_id and ce.arch_short_name = "contact.email"
  left outer join contact_details ced1 on ced1.contact_id = ce.contact_id and ced1.name = "emailAddress"
  left outer join entity_classifications ec on ec.entity_id = c.entity_id
  left outer join lookups l on l.lookup_id = ec.lookup_id and (l.arch_short_name = "lookup.customerAccountType" || l.arch_short_name = "lookup.customerType")
  join entity_relationships r on r.source_id=c.entity_id
  join entities p on r.target_id = p.entity_id
  left outer join entity_details d1 on d1.entity_id = p.entity_id and d1.name = "dateOfBirth"
  left outer join entity_details d2 on d2.entity_id = p.entity_id and d2.name = "desexed"
  left outer join entity_details d3 on d3.entity_id = p.entity_id and d3.name = "deceased"
  left outer join entity_details d4 on d4.entity_id = p.entity_id and d4.name = "species"
  left outer join lookups l4 on l4.code = d4.value and l4.arch_short_name = "lookup.species"
  left outer join entity_details d5 on d5.entity_id = p.entity_id and d5.name = "breed"
  left outer join lookups l5 on l5.code = d5.value and l5.arch_short_name = "lookup.breed"
where
  p.active = 1 and
  c.arch_short_name like "party.customer%" and
  c.active = 1 and
  l.name like $P{Classification} and
  l4.name like $P{Species} and
  d5.value like $P{Breed} and
  d1.value between $P{DOB Start} and $P{DOB End}
group by c.entity_id
order by c.name
   

I attempted to add  to the "where" clause

 d3.value = FALSE 

assuming that we wanted deceased - false however wierdly it didnt seem to work.