Customer Marketing Report

Submitted by Ben_Charlton on Tue, 08/08/2017 - 13:36

Implementation discussion

I was asked to put together a report which runs fine on small db's but bogs down on larger ones particularly if they run on windows server...

Any streamlining tips would be nice...

SQL is here... explaining it tends to show a lot of temp tables ....but I cant work out how to reduce that

select DISTINCT
    DATE_FORMAT(parent.activity_start_time,'%Y-%m-%d') as Transaction_date,
    r.source_id as trans_id,
    visit.source_id as visit_id,
    child.act_id as sale_id,
    product.entity_id as product_id,
    ##product.name as product_name,
    ##product_detail_name.value as alternate_name,
    IFNULL(product_detail_name.value,product.name) as product,
    IFNULL(template_detail_name.value,templates.name) as template,
    product.arch_short_name as arch,
    patient.entity_id as PatientID,
    patient.name as patient,
    species.value as species,
    weight.weight as weight,
    DATE_FORMAT(dob.value,'%Y-%m-%d') as dob,
    if(credit,-f.quantity, f.quantity) as quantity,
    if(credit,-f.total,f.total)as total
from acts parent 
    join act_relationships r on parent.act_id = r.source_id
    join acts child on r.target_id = child.act_id and child.arch_short_name like 'act.customerAccount%Item'
    left join act_relationships visit on visit.target_id = child.act_id and visit.arch_short_name = 'actRelationship.patientClinicalEventChargeItem'
    join participations product_partic on child.act_id = product_partic.act_id
    left join participations prod_temp_partic on child.act_id = prod_temp_partic.act_id and prod_temp_partic.arch_short_name = 'participation.productTemplate'
    left join entities templates on prod_temp_partic.entity_id = templates.entity_id and templates.arch_short_name = 'product.template'
    left join entity_details template_detail_name on templates.entity_id = template_detail_name.entity_id and template_detail_name.name = 'printedName'
    join entities product on product.entity_id = product_partic.entity_id and product.arch_short_name like 'product.%' and product.arch_short_name != 'product.template'
    left join entity_details product_detail_name on product.entity_id = product_detail_name.entity_id and product_detail_name.name = 'printedName'
    join participations patient_partic on child.act_id = patient_partic.act_id
    join entities patient on patient.entity_id = patient_partic.entity_id and patient.arch_short_name like 'party.patient%' 
    left join entity_details dob on dob.entity_id = patient.entity_id and dob.name = 'dateOfBirth'
    left join entity_details species on species.entity_id = patient.entity_id and species.name = 'species'
    join financial_acts f on f.financial_act_id = child.act_id
    left join 
       (SELECT DISTINCT
             DATE_FORMAT(weight.activity_start_time,'%Y-%m-%d') as wdate,
           weight.description as weight,
           patient.entity_id as id
         from acts weight 
            JOIN participations patient_partic on weight.act_id = patient_partic.act_id
            JOIN entities patient on patient.entity_id = patient_partic.entity_id and patient.arch_short_name like 'party.patient%' 
           WHERE weight.arch_short_name = 'act.patientWeight' GROUP BY weight.activity_start_time
        ) weight on weight.id = patient.entity_id and weight.wdate = DATE_FORMAT(parent.activity_start_time,'%Y-%m-%d')
where 
   (parent.activity_start_time between DATE( $P{startDate} ) and
   DATE( $P{endDate} ) and parent.arch_short_name like 'act.customer%')
order by parent.activity_start_time

Re: Customer Marketing Report

Submitted by tgething (not verified) on Tue, 08/08/2017 - 15:50.

Ben - I have a couple of suggestions:

a) in the 2nd last line of the SQL use "like 'act.customerAccountCharges%' " to tighten up the selection

b) in the weight subquery use $P{startDate} and $P{endDate} to limit the weights to just the applicable period, rather than the full practice history

When you are looking for the weight, do you need to look for any weight taken on day, or could you limit the weight acts to those associated with the visit (which will be faster)? [Or are you worried that there might be other visits on the day that contain just the weight.]

Regards, Tim G

Re: Customer Marketing Report

Submitted by tanderson on Fri, 11/08/2017 - 23:36.

Try the following:

SELECT
  DATE(parent.activity_start_time)                   AS Transaction_date,
  r.source_id                                        AS trans_id,
  visit.source_id                                    AS visit_id,
  child.act_id                                       AS sale_id,
  product.entity_id                                  AS product_id,
  IFNULL(product_detail_name.value, product.name)    AS product,
  IFNULL(template_detail_name.value, templates.name) AS template,
  product.arch_short_name                            AS arch,
  patient.entity_id                                  AS PatientID,
  patient.name                                       AS patient,
  species.value                                      AS species,
  (SELECT weight.description
   FROM acts weight
     JOIN participations patient_partic
       ON weight.act_id = patient_partic.act_id
          AND patient_partic.activity_start_time BETWEEN DATE($P{startDate}) AND DATE($P{endDate})
   WHERE weight.arch_short_name = 'act.patientWeight' AND patient_partic.entity_id = patient.entity_id
         AND weight.activity_start_time BETWEEN DATE($P{startDate}) AND DATE($P{endDate})
         AND DATE(parent.activity_start_time) = DATE(weight.activity_start_time)
   ORDER BY weight.activity_start_time DESC
   LIMIT 1)                                          AS weight,
  DATE_FORMAT(dob.value, '%Y-%m-%d')                 AS dob,
  IF(credit, -f.quantity, f.quantity)                AS quantity,
  IF(credit, -f.total, f.total)                      AS total
FROM acts parent
  JOIN act_relationships r
    ON parent.act_id = r.source_id
       AND r.arch_short_name IN
           ('actRelationship.customerAccountInvoiceItem', 'actRelationship.customerAccountCounterItem', 'actRelationship.customerAccountCreditItem')
  JOIN acts child ON r.target_id = child.act_id
  LEFT JOIN act_relationships visit
    ON visit.target_id = child.act_id AND visit.arch_short_name = 'actRelationship.patientClinicalEventChargeItem'
  JOIN participations product_partic
    ON child.act_id = product_partic.act_id
       AND product_partic.arch_short_name = 'participation.product'
  LEFT JOIN participations prod_temp_partic
    ON child.act_id = prod_temp_partic.act_id
       AND prod_temp_partic.arch_short_name = 'participation.productTemplate'
  LEFT JOIN entities templates
    ON prod_temp_partic.entity_id = templates.entity_id
       AND templates.arch_short_name = 'product.template'
  LEFT JOIN entity_details template_detail_name
    ON templates.entity_id = template_detail_name.entity_id
       AND template_detail_name.name = 'printedName'
  JOIN entities product ON product.entity_id = product_partic.entity_id
  LEFT JOIN entity_details product_detail_name
    ON product.entity_id = product_detail_name.entity_id
       AND product_detail_name.name = 'printedName'
  JOIN participations patient_partic
    ON child.act_id = patient_partic.act_id
       AND patient_partic.arch_short_name = 'participation.patient'
  JOIN entities patient ON patient.entity_id = patient_partic.entity_id
  LEFT JOIN entity_details dob
    ON dob.entity_id = patient.entity_id
       AND dob.name = 'dateOfBirth'
  LEFT JOIN entity_details species
    ON species.entity_id = patient.entity_id
       AND species.name = 'species'
  JOIN financial_acts f
    ON f.financial_act_id = child.act_id
WHERE parent.arch_short_name IN
      ('act.customerAccountChargesInvoice', 'act.customerAccountChargesCredit', 'act.customerAccountChargesCounter')
      AND parent.activity_start_time BETWEEN DATE($P{startDate}) AND DATE($P{endDate})
ORDER BY parent.activity_start_time

OpenVPMS

Customer Marketing Report

Comment viewing options

Re: Customer Marketing Report

Re: Customer Marketing Report