Home › Forums › implementation › Implementation discussion ›

Date deactivated

Submitted by Guest on Tue, 18/08/2015 - 05:55
in

I am currently developing a Key Performance Indicator report.

One of the numbers of interest is the number of customers.

For the customer we have an active flag and a dateCreated.  However when the customer is deactivated, there is no dateDeactivated. Hence one cannot calculate for a historical period how many non-deactivated customers the business had.  What you need is:

count customers with dateCreated < periodEnd and (isnull (dateDeactivated) or dateDeactivated > periodEnd)

We need to add a dateDeactivated (hidden) to party.customerperson which is set when the Active flag is cleared and set to null if Active goes from false to true (ie the customer is re-activated).

For customers already deactivated, we could use some SQL to set the dateDeactivated to the date of the last act for that customer.

Comments?

Regards, Tim G

‹ Date-stamping a backup file templates ›

Comment viewing options

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

Re: Date deactivated

Submitted by tanderson on Tue, 18/08/2015 - 21:07.

As you've said, you could determine the deactivation date based on the date of the customer's last act. This will work in all versions of OpenVPMS, and not require the addition of a new node.

E.g.:

select count(*)
from (
    select e.entity_id, e.name, e.active, d.value createdDate,
           case when not e.active
                then (select max(a.activity_start_time)
                      from acts a
                      join participations p
                         on p.entity_id = entity_id
                         and a.act_id = p.act_id
                         and p.arch_short_name = "participation.customer")
            end as deactivatedDate
    from entities e
    join entity_details d
         on e.entity_id = d.entity_id
         and d.name = "createdDate"
    where e.arch_short_name = "party.customerperson") c
where c.createdDate <= "2014-01-31"
    and (c.deactivatedDate is null or c.deactivatedDate > "2014-01-31");

The inner select provides a listing of customers and their created and deactivated dates:

    select e.entity_id, e.name, e.active, d.value createdDate,
           case when not e.active
                then (select max(a.activity_start_time)
                      from acts a
                      join participations p
                         on p.entity_id = entity_id
                         and a.act_id = p.act_id
                         and p.arch_short_name = "participation.customer")
            end as deactivatedDate
    from entities e
    join entity_details d
         on e.entity_id = d.entity_id
         and d.name = "createdDate"
    where e.arch_short_name = "party.customerperson"

 

 

 

Re: Date deactivated

Submitted by tgething (not verified) on Wed, 19/08/2015 - 02:13.

Tim - thanks for the SQL. I have saved it for use in the KPI report. I will also clone a version for patients.

Regards, Tim G

Re: Date deactivated

Submitted by tgething (not verified) on Wed, 26/08/2015 - 11:24.

Tim - It struck me that for the purpose of 'how many patients/customers at date xxxx' I do not need a deactivated date.

Your code (adjusted) is:

select count(*)
from (
    select e.entity_id, e.active, cast(d.value as Date) as createdDate,
           case when not e.active
                 then (select max(p.activity_start_time)
                      from  participations p
                         where p.entity_id = e.entity_id
                          and p.arch_short_name = "participation.patient")
            end as deactivatedDate
    from entities e
    join entity_details d
         on e.entity_id = d.entity_id
         and d.name = "createdDate"
    where e.arch_short_name = "party.patientpet") c
where c.createdDate <= '2013-01-31'
    and (c.deactivatedDate is null or c.deactivatedDate >  '2013-01-31');

If we adjust to just check for any activity after the end date we get:

select count(*)
from entities e
   join entity_details d
         on e.entity_id = d.entity_id
         and d.name = 'createdDate'
where e.arch_short_name = 'party.patientpet'
    and cast(d.value as Date) <= '2013-01-31'
    and (e.active=1
        or exists ( select *
                      from  participations p
                      where p.entity_id = e.entity_id
                          and p.arch_short_name = 'participation.patient'
                          and p.activity_start_time > '2013-01-31')
         );

These both provide the same answer, but there is a significant performance improvement: 28.8 sec down to 4.7 sec - peanuts if you have to do it once but for the KPI report I need to do it 16 times (count at start and end of period for 4 periods for both customers and patients).

Regards, Tim G

Syndicate content