Patient List Report

Submitted by Guest on Wed, 06/11/2013 - 11:43

General User's Discussion

Hi everyone,

I'd just like some clarification on how to interpret the results of the patient list report with regards to the start and end dates.

I presume these are date ranges for the birth date of the animal, but what I want clarified is if one does a search (for example) for all canines born in 2010, do the results come up with EVERY dog born in 2010 that is on our system or every dog born in 2010 that was also ADDED to our system ONLY in 2010?

I hope this makes sense. The question came up when we were looking at seeing how many new pets were added in a certain time frame compared to euthanased animals (ie to see if owners were replacing euthanased pets).

thanks

Sarah

Re: Patient List Report

Submitted by tanderson on Wed, 06/11/2013 - 13:19.

In your example, it will return all dogs born in 2010.

Note that this report will exclude patients that don't have records for:

Birth Date
Species
Breed
Colour
Sex
Desex status

If you have migrated from another PMS, some of these values may not be present, so you might have patients with a Birth Date of 2010 that don't show up in the report.

Regards,

Tim

Re: Patient List Report

Submitted by tgething (not verified) on Wed, 06/11/2013 - 13:53.

Tim A - can I beg to differ. The SQL code is as follows:

select
   c.name as customer,
  c.description as description,
  p.name as patient,
  d1.value as dateOfBirth,
  ls.name as species,
  lb.name as breed
from entities p
  join entity_relationships r on r.target_id=p.entity_id
  join entities c on r.source_id = c.entity_id
  left outer join entity_details d1 on d1.entity_id = p.entity_id and d1.name = "dateOfBirth"
  join entity_details d2 on d2.entity_id = p.entity_id and d2.name = "species"
  join lookups ls on ls.code = d2.value and ls.arch_short_name = "lookup.species"
  join entity_details d3 on d3.entity_id = p.entity_id and d3.name = "breed"
  join lookups lb on lb.code = d3.value and lb.arch_short_name = "lookup.breed"
where
  p.arch_short_name like "party.patientpet%" and
  P.active = 1 and
  c.arch_short_name like "party.customer%" and
  ls.name like $P{species} and
  lb.name like $P{breed} and
  ((d1.value between $P{startDate} and $P{endDate}) or (d1.value is null))
order by c.name

I would agree with you as regards species and breed, but the DOB uses a left join, and so will include patients with a null DOB - and you can see in the 2nd last line it selects those with a DOB between the specified dates or no DOB set. Note that 'date between X and Y' means date >= X and <= Y.

Regards, Tim G