Home › Forums › Users › General User's Discussion ›

Report for unvaccinated pets

Submitted by Ralph Hudson on Thu, 10/11/2011 - 16:36
in

Hi, is there a report that will list pets that have not been vaccinated ever or are say 2 years overdue?

Would like to send out a marketing letter to overdue pets.

Ralph

‹ Multiple client/patient charge Statistics Reminder Report ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Fri, 11/11/2011 - 00:37.

Hi Ralph,

Are you tracking this via reminders, or via product sales? (or both?)

Cheers, Paul

Re: Report for unvaccinated pets

Submitted by Ralph Hudson on Fri, 11/11/2011 - 19:21.

Hi Paul,

I'm new to open, in previous software i have used you could check for unvaccinated pets, or dogs that had not used a particular product. So not sure if this is possible, or do you use the report for clients who have not visited in the past year?

Cheers Ralph

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Sat, 12/11/2011 - 00:57.

Can anyone confirm this SQL? I think this should get what Ralph's looking for... but I'm still learning the schema myself.

This generates a list of patient entity IDs that have purchased a product in the specified date range. Then, it returns all patients (joined with owners) that are not in that list.

If this looks right to the schema gurus... I can throw it in a report... since I'm on a report writing kick presently.

select owners.name,patients.name
from entities patients
inner join entity_relationships
on entity_relationships.target_id=patients.entity_id
inner join entities owners
on entity_relationships.source_id=owners.entity_id
where patients.entity_id not in
    (select patientcharges.entity_id from
        (select * from participations p1
        where p1.arch_short_name="participation.patient"
        and p1.act_arch_short_name="act.customerAccountInvoiceItem"
        and p1.activity_start_time>DATE("2011-10-01")
        ) patientcharges
    inner join participations patientproducts
    on patientproducts.act_id=patientcharges.act_id
    inner join entities productentities
    on patientproducts.entity_id=productentities.entity_id
    where productentities.name like "%Amox%")
and patients.arch_short_name="party.patientpet"
and patients.active=1

Re: Report for unvaccinated pets

Submitted by tanderson on Sun, 13/11/2011 - 00:18.

You probably need to added a join on patient species, to restrict the results further.

Also, there can be multiple owner relationships for a patient. The active one is the one with no active_end_time or an active_end_time greater than the current date. For simplicity, I've stuck with the former.

 The following lists all CANINE patients that have never had a C5 Vaccination:

 select patient.name, customer.name
from entities patient
join entity_details species on patient.entity_id = species.entity_id and species.name = "species" and species.value = "CANINE"
join entity_relationships owner_rel on owner_rel.target_id = patient.entity_id and owner_rel.arch_short_name = "entityRelationship.patientOwner"
    and owner_rel.active_end_time is null
join entities customer on owner_rel.source_id = customer.entity_id
where patient.entity_id not in (
    select patient_partic.entity_id
    from entities product
    join participations product_partic on product.entity_id = product_partic.entity_id and product_partic.act_arch_short_name = "act.customerAccountInvoiceItem"
        and product_partic.arch_short_name = "participation.product"
    join participations patient_partic on patient_partic.act_id = product_partic.act_id
        and patient_partic.arch_short_name = "participation.patient"
    where product.name = "C5 Vaccination")

Re: Report for unvaccinated pets

Submitted by tanderson on Sun, 13/11/2011 - 22:33.

The above doesn't perform too well on large databases, due to the way MySQL performs query optimisations. It also doesn't handle the case where a patient has never been invoiced.

The following corrects that, and should run in a fraction of the time on MySQL 5.1.

 select patient.name, customer.name
from entities patient
join entity_details species on patient.entity_id = species.entity_id
    and species.name = "species" and species.value = "CANINE" and patient.active = 1
join entity_relationships owner_rel on owner_rel.target_id = patient.entity_id and owner_rel.arch_short_name = "entityRelationship.patientOwner"
    and owner_rel.active_end_time is null
join entities customer on owner_rel.source_id = customer.entity_id
left join participations patient_partic on patient_partic.entity_id = patient.entity_id and patient_partic.arch_short_name = "participation.patient"
    and patient_partic.act_arch_short_name = "act.customerAccountInvoiceItem"
left join participations product_partic on product_partic.act_id = patient_partic.act_id
    and product_partic.arch_short_name = "participation.product"
left join entities product on product.entity_id = product_partic.entity_id
GROUP BY patient.entity_id
HAVING sum(CASE WHEN product.name="C5 Vaccination" THEN 1 ELSE 0 END) = 0
order by customer.name, patient.name

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Fri, 18/11/2011 - 02:02.

Hi Tim,

I was just taking a look at this. The above should handle the case that the patient hasn't been invoiced... because it's doing a "not in"... so it's basically all patients, minus those who were invoiced for the item within the time period specified. I'm not sure how to apply the time period requirement to the query you have there since it's a positive query for the items. I had later modified it to this below.

You're right about the performance... the subquery kills it. I also didn't have the species in there.

Cheers, Paul

 

 select
    owners.name as owner,
    patients.name as patient
from entities patients
inner join entity_relationships
on entity_relationships.target_id=patients.entity_id
inner join entities owners
on entity_relationships.source_id=owners.entity_id
where patients.entity_id not in
(select distinct patientcharges.entity_id from
(select * from participations p1
where p1.arch_short_name="participation.patient"
and p1.act_arch_short_name="act.customerAccountInvoiceItem"
    and p1.activity_start_time>=$P{startDate}
    and p1.activity_start_time<=date_add($P{endDate},INTERVAL "23:59:59" HOUR_SECOND)
) patientcharges
inner join participations patientproducts
on patientproducts.act_id=patientcharges.act_id
inner join entities productentities
on patientproducts.entity_id=productentities.entity_id
where productentities.name like $P{searchString})
and patients.arch_short_name="party.patientpet"
and patients.active=1

 

Re: Report for unvaccinated pets

Submitted by tanderson on Fri, 18/11/2011 - 16:39.

You're right about it being able to handle patients that haven't been invoiced. No idea what I was thinking when I wrote that.

To find all patients that have never had a particular vaccine or haven't had it in the last 2 years, you could change the HAVING clause e.g.:

 HAVING sum(CASE WHEN product.name="Vaccination - C5" and patient_partic.activity_start_time > "2009-11-18" THEN 1 ELSE 0 END) = 0

-Tim

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Sat, 19/11/2011 - 05:39.

Nice... I've thrown it into a report.

Ralph: Can you see if this works for you?

Cheers, Paul

AttachmentSize
patients without product.jrxml 12.59 KB

Re: Report for unvaccinated pets

Submitted by Ralph Hudson on Wed, 30/11/2011 - 13:23.

Thanks I have tried it and it works except it does every pet who has ever been recorded on our system, going back 20 years.Is it possible to have a date, age or transaction filter so can look at clients or pets that have visited in the last 2 years?

Thanks

Ralph

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Thu, 01/12/2011 - 00:14.

Hi Ralph,

As per the original request: "report that will list pets that have not been vaccinated ever or are say 2 years overdue?"... that would definitely include pets that go back 20 years. We'll have to add another filter to track that one.

Cheers, Paul

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Thu, 01/12/2011 - 02:57.

Tim, how does this look to you?

Cheers, Paul

 

select
    patient.entity_id as id, patient.name as patient, customer.name as owner
from entities patient
join entity_details species
    on patient.entity_id=species.entity_id
    and species.name="species"
    and species.value like "%"
    and patient.active=1
join entity_relationships owner_rel
    on owner_rel.target_id=patient.entity_id
    and owner_rel.arch_short_name="entityRelationship.patientOwner"
    and owner_rel.active_end_time is null
join entities customer
    on owner_rel.source_id=customer.entity_id
    and customer.active=1
left join participations patient_partic
    on patient_partic.entity_id=patient.entity_id
    and patient_partic.arch_short_name="participation.patient"
    and patient_partic.act_arch_short_name="act.customerAccountInvoiceItem"
left join participations product_partic
    on product_partic.act_id=patient_partic.act_id
    and product_partic.arch_short_name="participation.product"
left join entities product
    on product.entity_id=product_partic.entity_id
left join participations visit_partic
    on visit_partic.entity_id=patient.entity_id
    and visit_partic.arch_short_name="participation.patient"
    and visit_partic.act_arch_short_name="act.patientClinicalEvent"
GROUP BY patient.entity_id
HAVING sum(CASE WHEN product.name like "%vac%"
    and product_partic.activity_start_time>=DATE("2011/10/1")
    and product_partic.activity_start_time<=date_add(DATE("2011/12/1"),INTERVAL "23:59:59" HOUR_SECOND)
    and visit_partic.activity_start_time>=DATE("2011/9/1")
    and visit_partic.activity_start_time<=date_add(DATE("2011/12/1"),INTERVAL "23:59:59" HOUR_SECOND)
    THEN 1 ELSE 0 END)=0
order by customer.name, patient.nameselect
 

Re: Report for unvaccinated pets

Submitted by tanderson on Thu, 01/12/2011 - 11:37.

Disclaimer: I haven't tested this, so your mileage may vary.

The above will list all active patients that have an active current owner that haven't been vaccinated in a particular date range. If they have been vaccinated prior to of after the date range, then they will be included in the list.

It should include all patients on the system that aren't deceased. If however the data migration didn't deactivate deceased patients, then these will be included.

I'm not sure that the visit participation join helps - if a patient has been invoiced in the date range, then they should have a visit participation as well. I'm assuming you meant the product and visit participations to have the same activity_start_time?

To ensure that deceased patients are excluded, you could add:

 left join entity_details deceased on patient.entity_id = deceased.entity_id
    and deceased.name = "deceased"

....

 where deceased.value = "false" or deceased.value is null

The requirement:

... date, age or transaction filter so can look at clients or pets that have visited in the last 2 years

is partially met by the above. To only query those patients that have visited in the last 2 years, you would replace the left join constraint on patient_partic with a join constraint. This would mean that those patients that have visited (i.e been invoiced) would be reported on. It would not take into account a customer's other patients that haven't been invoiced.

You can filter by birth date (and determine a patient's age) with:

 select birthDate.value as birthDate, datediff(now(), DATE(birthDate.value)) / 365 as age
from entities patient
left join entity_details birthDate on patient.entity_id = birthDate.entity_id
    and birthDate.name = "dateOfBirth"
where patient.arch_short_name = "party.patientpet"

-Tim

Re: Report for unvaccinated pets

Submitted by garry edgar on Wed, 30/11/2011 - 21:18.

Can someone outline what you are filling in each of the fields on the report for? 

 

I tried it with the date range of 1-11-1996 to 30-11-2004 and the product of %vac% and canine.( I was assuming this would be date of birth range)

 

I got some deceased animals and I also got some animals who were born after 2004 and hence, never vaccinated within the date range(?)

 

If I knew what to do to write one of these, I would add selections for age, species and exclusion of the service for life(which would probably have to have a date range from year 1 to present day or would have to be implied in the report somehow).

Re: Report for unvaccinated pets

Submitted by pstadler (not verified) on Thu, 01/12/2011 - 00:19.

Hi Garry,

The date range is the time period during which the '%vac%' products were sold... not the DOB. It sounds like you're looking for a different report than the one originally discussed here. There wasn't any discussion of DOBs previously.

It shouldn't report deceased animals though... I'll try to take a look at that. Unfortunately, I'm squeezing some help on this in with my day-job (I'm just a community member, not a developer)...

Cheers, Paul

Syndicate content