Adding patient record completeness to KPI export

[nikyraja]

@mbarton and I discussed at a platform meeting adding another variable onto the KPI export to show the number of records on the system and how many had been completed. Ie, the information displayed here on each organisation dashboard:

screenshot from staging site

Could this please be added to the KPI export? This could either be by creating a separate tab or by adding this score as another row after the KPIs - whichever is easiest for you. The data is needed at Trust/Health Board, ICB, NHSE Region, OPEN UK, country and national.

Let me know if you have any questions

[mbarton]

This is taking a bit longer than planned as we don't have any existing code that calculates the totals for multiple aggregation levels at once and I'm not keen on adding a brute force loop

[mbarton]

Not 100% sure how to to translate these to the Django ORM but I think these queries are on the right track

/* organisation */
select organisation.name, count(reg.id) as count
from epilepsy12_organisation organisation
left join epilepsy12_site site on organisation.id = site.organisation_id
inner join epilepsy12_registration reg on site.case_id = reg.case_id
where reg.cohort = 6
/* TODO: add completed filters */
group by organisation.name
order by count desc;

/* trust */
select trust.name, count(reg.id) as count
from epilepsy12_organisation organisation
left join epilepsy12_site site on organisation.id = site.organisation_id
inner join epilepsy12_registration reg on site.case_id = reg.case_id
inner join epilepsy12_trust trust on trust.id = organisation.trust_id
where reg.cohort = 6
/* TODO: add completed filters */
group by trust.name
order by count desc;

When I run them on my locally seeded instance though I get one more case than shown on the front page

[mbarton]

I can replicate up to the joins easily in the ORM:

>>> Organisation.objects.annotate(count=Count("site")).first().count
25

Ignore me I was a noodle, the relation is organisation in the model definition not registration. If I try and walk the relations though I get an error:

Organisation.objects.annotate(count=Count("site__registration")).first().count

Traceback (most recent call last):          
  File "<console>", line 1, in <module>     
  File "/usr/local/lib/python3.11/site-packages/django/db/models/manager.py", line 87, in manager_method                                                                                                           
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1630, in annotate
    return self._annotate(args, kwargs, select=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1680, in _annotate
    clone.query.add_annotation(
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1194, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/aggregates.py", line 63, in resolve_expression
    c = super().resolve_expression(query, allow_joins, reuse, summarize)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/expressions.py", line 1073, in resolve_expression
    c.source_expressions[pos] = arg.resolve_expression(
                                ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/expressions.py", line 895, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 2026, in resolve_ref
    transform = join_info.transform_function(targets[0], final_alias)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1886, in transform
    return self.try_transform(wrapped, name)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 1423, in try_transform
    raise FieldError(
django.core.exceptions.FieldError: Unsupported lookup 'registration' for BigAutoField or join on the field not permitted.

[mbarton]

Ah the out by one error was because I hadn't added site_is_primary_centre_of_epilepsy_care as a filter to my query so I was including a patient in the test dataset who had Princess Royal University Hospital in their list of organisations which is also under Kings trust

[mbarton]

This seems to work:

select
    trust.name,
    (prog.registration_complete
        and prog.assessment_complete
        and prog.epilepsy_context_complete
        and prog.multiaxial_diagnosis_complete
        and prog.investigations_complete
        and prog.management_complete) as complete,
    count(*) as count
from epilepsy12_organisation organisation
left join epilepsy12_site site on organisation.id = site.organisation_id
inner join epilepsy12_registration reg on site.case_id = reg.case_id
inner join epilepsy12_auditprogress prog on reg.audit_progress_id = prog.id 
inner join epilepsy12_trust trust on trust.id = organisation.trust_id
where reg.cohort = 6
    and site.site_is_primary_centre_of_epilepsy_care = true
group by
    trust.name,
    (prog.registration_complete
        and prog.assessment_complete
        and prog.epilepsy_context_complete
        and prog.multiaxial_diagnosis_complete
        and prog.investigations_complete
        and prog.management_complete)
order by count desc;

I tried avoiding repeating the definition of complete with a subquery or a WITH clause but the numbers came out wrong.

I don't think it's possible to write this query using the Django ORM directly as you can't walk down from Trust to Organisation to do annotations, only the other way round. It could just be my lack of experience though.

[eatyourpeas]

Might be one for us to chat about

On Mon, 7 Oct 2024, 15:04 Michael Barton, @.***> wrote:

This seems to work:

select trust.name, (prog.registration_complete and prog.assessment_complete and prog.epilepsy_context_complete and prog.multiaxial_diagnosis_complete and prog.investigations_complete and prog.management_complete) as complete, count(*) as countfrom epilepsy12_organisation organisationleft join epilepsy12_site site on organisation.id = site.organisation_idinner join epilepsy12_registration reg on site.case_id = reg.case_idinner join epilepsy12_auditprogress prog on reg.audit_progress_id = prog.id inner join epilepsy12_trust trust on trust.id = organisation.trust_idwhere reg.cohort = 6 and site.site_is_primary_centre_of_epilepsy_care = truegroup by trust.name, (prog.registration_complete and prog.assessment_complete and prog.epilepsy_context_complete and prog.multiaxial_diagnosis_complete and prog.investigations_complete and prog.management_complete)order by count desc;

I tried avoiding repeating the definition of complete with a subquery or a WITH clause but the numbers came out wrong.

I don't think it's possible to write this query using the Django ORM directly as you can't walk down from Trust to Organisation to do annotations, only the other way round. It could just be my lack of experience though.

— Reply to this email directly, view it on GitHub https://github.com/rcpch/rcpch-audit-engine/issues/1022#issuecomment-2397029288, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAICOV4DILXG5LNHKILLZK3Z2KIHBAVCNFSM6AAAAABMSMYTWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGOJXGAZDSMRYHA . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[mbarton]

@eatyourpeas to have a look at doing this within the Django ORM.

3 options discussed IRL:

• Get it working in the Django ORM (maybe subquery?)
• Write the numbers into the KPI aggregation models at the various levels
• Raw SQL the queries

[eatyourpeas]

@nikyraja this adds several new sheets to the .xlsx . Do please check looks ok.

[nikyraja]

thank you both, that looks good!

Is there an easy way to get a percentage column? Not a priority, as we can very easily calculate in excel, so can leave this till after the organisational audit if needed.

[eatyourpeas]

Sure @nikyraja that is live now