Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
patient_standard_care_reminder_idPrimary key and patient standard care reminder unique identifier.id
createdThe timestamp when the standard care reminder record was created.
modifiedThe timestamp when the standard care reminder record was last modified.
is_deletedIndicates whether the standard care reminder record is deleted.
is_declinedIndicates whether the standard care reminder has been declined.
statusThe current status of the reminder (e.g., ACTIVE, INACTIVE, NOT_MEDICALLY_NECESSARY, DECLINED).status
organization_patient_idGlobal identifier that represents a unique patient across all clinics in the system (from patient table).organization_patient_id
organization_client_idGlobal identifier that represents a unique client across all clinics in the system.organization_client_id
clinic_idForeign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the reminder.clinic_id
patient_standard_care_idForeign key to the patient_standard_care table. The identifier of the patient standard care association.patient_standard_care_id
standard_care_reminder_idForeign key to the standard_care_reminder table. The identifier of the standard care reminder definition.standard_care_reminder_id
kindThe type of care reminder. Always ‘STANDARD’ for standard care reminders.kind
standard_care_reminder_nameThe name of the standard care reminder (from the linked standard_care_reminder table).name
last_fulfilled_dateThe date the reminder was last fulfilled.last_fulfilled_date
due_dateThe due date of the reminder. Uses the manually set due date if available, otherwise the automatic due date.due_date
is_past_dueIndicates whether the reminder is past due (due date is before the current date).is_past_due
last_updatedThe timestamp when the standard care reminder record was last updated in the source table. This is the most recent timestamp from all related tables.
vetcove_corporate_idThe unique identifier of the corporate group associated with this record.

​
Relationships

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of patient standard care reminders
    pre-joined for easy consumption, mirroring the standard care reminders API.
*/

"
    )
}}

with patient_standard_care_reminders as (
    -- Limit to one row per patient_standard_care_reminder_id
    select * from {{ ref('patient_standard_care_reminder') }}
    qualify
        row_number()
            over (
                partition by id
                order by patient_id
            )
        = 1
    order by id
)

, standard_care_reminders as (
    select * from {{ ref('standard_care_reminder') }}
)

, patients as (
    select * from {{ ref('patient') }}
)

, clients as (
    select * from {{ ref('client') }}
)

select
    -- Identifiers
    patient_standard_care_reminders.id as patient_standard_care_reminder_id
    , patient_standard_care_reminders.created
    , patient_standard_care_reminders.modified
    -- Status Flags
    , patient_standard_care_reminders.is_deleted
    , patient_standard_care_reminders.is_declined
    -- Identifiers
    , patient_standard_care_reminders.status
    , patients.organization_patient_id
    , clients.organization_client_id
    , patient_standard_care_reminders.clinic_id
    , patient_standard_care_reminders.patient_standard_care_id
    , patient_standard_care_reminders.standard_care_reminder_id
    -- Details
    , 'STANDARD' as kind
    , standard_care_reminders.name as standard_care_reminder_name
    , patient_standard_care_reminders.last_fulfilled_date
    , coalesce(
        patient_standard_care_reminders.manual_due_date
        , patient_standard_care_reminders.automatic_due_date
    ) as due_date
    , case
        when due_date < current_date() then true
        else false
    end as is_past_due
    -- Timestamps
    , greatest(
        coalesce(patient_standard_care_reminders.last_updated, '1900-01-01')
        , coalesce(standard_care_reminders.last_updated, '1900-01-01')
        , coalesce(patients.last_updated, '1900-01-01')
        , coalesce(clients.last_updated, '1900-01-01')
    ) as last_updated
    -- Corporate Filtering
    , patient_standard_care_reminders.vetcove_corporate_id
from patient_standard_care_reminders
left join standard_care_reminders
    on patient_standard_care_reminders.standard_care_reminder_id = standard_care_reminders.id
left join patients
    on patient_standard_care_reminders.patient_id = patients.id
left join clients
    on patient_standard_care_reminders.client_id = clients.id