Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
appointment_idPrimary key and appointment unique identifier.id
createdThe timestamp when the appointment record was created.
modifiedThe timestamp when the appointment record was last modified.
is_deletedIndicates whether the appointment record is deleted.
calendar_idForeign key to the calendar table. The identifier of the calendar associated with the appointment.calendar_id
clinic_idForeign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the appointment.clinic_id
calendar_nameThe name of the calendar associated with the appointment.
resource_idForeign key to the calendar_resource table. The identifier of the calendar resource associated with the appointment.resource_id
resource_nameThe name of the calendar resource / provider assigned to the appointment.resource_name
kind_idForeign key to the appointment_kind table. The identifier of the appointment kind.
kindThe display name of the appointment kind. Uses the custom name if set, otherwise falls back to the slug (from appointment_kind table).kind
clinic_patient_idForeign key to the patient & client_enriched table. The identifier of the patient associated with the appointment.clinic_patient_id
clinic_client_idForeign key to the client & client_enriched table. The identifier of the client associated with the appointment.clinic_client_id
medical_record_idForeign key to the medical_record & medical_record_enriched table. The identifier of the medical record associated with this appointment, if any.medical_record_id
start_datetimeThe start date and time of the appointment.start_datetime
end_datetimeThe end date and time of the appointment.end_datetime
statusThe current status of the appointment (e.g., IS_NEW_CLIENT, IS_NEW_PATIENT, IS_CARE_PLAN, IS_LAST_NO_SHOW, IS_LAST_CANCELLED, IS_DEPOSIT_PAID, IS_INTAKE_COMPLETE, IS_ON_INSURANCE).status
clinic_notesRaw JSON clinic notes for the appointment.clinic_notes
reasonThe reason for the appointment.reason
dropoff_start_datetimeThe start date and time the patient for this appointment will be dropped off, if applicable.dropoff_start_datetime
pickup_start_datetimeThe start date and time the patient for this appointment will be picked up, if applicable.pickup_start_datetime
alert_tagsA list of alert tags associated with the appointment.alert_tags
cancelation_reasonThe reason for the appointment’s cancellation or no-show.cancelation_reason
followup_appointment_idsArray of appointment IDs that are followups to this appointment (appointments whose followup_for_medical_record points to this appointment’s medical record). Empty array when none.followup_appointment_ids
last_updatedThe timestamp when the appointment record was last updated in the source table. This is the most recent timestamp from all related tables (appointment, calendar, appointment_kind, calendar_resource, medical_record). Note: followup_appointment_ids are derived from the appointment table already included above.
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 appointment data
    mirroring the appointment API.
*/

"
    )
}}

with appointment as (
    select * from {{ ref('appointment') }}
)

, calendars as (
    select * from {{ ref('calendar') }}
)

, appointment_kinds as (
    select * from {{ ref('appointment_kind') }}
)

, calendar_resources as (
    select * from {{ ref('calendar_resource') }}
)

-- Associated medical record
-- Only pull in non-deleted records to avoid duplicates
, medical_record as (
    select * from {{ ref('medical_record') }}
    where
        appointment_id is not null
        and is_deleted = false
)

-- Followup appointment IDs by medical record
, followup_appointment_ids as (
    select
        followup_for_medical_record_id as medical_record_id
        , array_agg(id) within group (
            order by id
        ) as followup_appointment_ids
    from appointment
    where
        followup_for_medical_record_id is not null
        and is_deleted = false
    group by followup_for_medical_record_id
)

select
    appointment.id as appointment_id
    , appointment.created
    , appointment.modified
    , appointment.is_deleted
    , appointment.calendar_id
    , calendars.clinic_id
    , calendars.name as calendar_name
    , appointment.calendar_resource_id as resource_id
    , calendar_resources.name as resource_name
    , appointment.appointment_kind_id as kind_id
    , case
        when
            coalesce(appointment_kinds.custom_name, '') = ''
            then appointment_kinds.slug
        else appointment_kinds.custom_name
    end as kind
    , appointment.patient_id as clinic_patient_id
    , appointment.client_id as clinic_client_id
    , medical_record.id as medical_record_id
    , appointment.start_datetime
    , appointment.end_datetime
    , appointment.status
    , appointment.clinic_notes_json as clinic_notes
    , appointment.reason
    , appointment.dropoff_start_datetime
    , appointment.pickup_start_datetime
    , appointment.alert_tags
    , appointment.cancelation_reason
    , coalesce(
        followup_appointment_ids.followup_appointment_ids
        , array_construct()
    ) as followup_appointment_ids
    , greatest(
        coalesce(appointment.last_updated, '1900-01-01')
        , coalesce(calendars.last_updated, '1900-01-01')
        , coalesce(appointment_kinds.last_updated, '1900-01-01')
        , coalesce(calendar_resources.last_updated, '1900-01-01')
        , coalesce(medical_record.last_updated, '1900-01-01')
    ) as last_updated
    , appointment.vetcove_corporate_id
from appointment
left join calendars
    on appointment.calendar_id = calendars.id
left join appointment_kinds
    on appointment.appointment_kind_id = appointment_kinds.id
left join calendar_resources
    on appointment.calendar_resource_id = calendar_resources.id
left join medical_record
    on appointment.id = medical_record.appointment_id
left join followup_appointment_ids
    on medical_record.id = followup_appointment_ids.medical_record_id