referral_summary

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

uuid

2147483647

√

null

referral unique identifier

sent_at

timestamptz

35,6

√

null

when the referral was sent

sent_by

text

2147483647

√

null

referral sent by

reference_number

text

2147483647

√

null

referral number

crn

text

2147483647

√

null

service user crn

created_by

text

2147483647

√

null

id of the person creating the referral

assigned_to_id

text

2147483647

√

null

referral assigend to id

assigned_auth_source

text

2147483647

√

null

auth source of the assigned person

assigned_user_name

text

2147483647

√

null

name of the assigned person

sent_auth_source

text

2147483647

√

null

source of the sent person

sent_user_name

text

2147483647

√

null

name of the sent person

service_user_first_name

text

2147483647

√

null

first name of the service user

service_user_last_name

text

2147483647

√

null

last name of the service user

service_provider_id

varchar

√

null

service_provider.id

Implied ConstraintR

service provider id

service_provider_name

text

2147483647

√

null

service provider name

intervention_title

text

2147483647

√

null

intervention title

concluded_at

timestamptz

35,6

√

null

referral concluded at

expected_release_date

date

√

null

referral expected release date

is_referral_releasing_in12weeks

bool

√

null

is referral releasing in 12 weeks

referral_type

person_current_location_type

2147483647

√

null

referral type

prison_id

text

2147483647

√

null

prison id where referral is in

probation_office

text

2147483647

√

null

probation office where referral is in

pdu

text

2147483647

√

null

pdu where referral is in

ndelius_pdu

text

2147483647

√

null

pdu stored in delius

contract_id

uuid

2147483647

√

null

dynamic framework contract id

end_requested_at

timestamptz

35,6

√

null

referral end requested at

eosr_id

uuid

2147483647

√

null

referral end of service id

appointment_id

uuid

2147483647

√

null

supplier assessment appointment id

Relationships

Close relationships within degrees of separation

View Definition

WITH cte AS (
         SELECT referral_assignments.referral_id,
            referral_assignments.assigned_to_id,
            rank() OVER (PARTITION BY referral_assignments.referral_id ORDER BY referral_assignments.assigned_at DESC) AS current_assignment_rank
           FROM referral_assignments
        ), supplier_appointment AS (
         SELECT a.referral_id,
            a.id,
            rank() OVER (PARTITION BY a.referral_id ORDER BY a.appointment_time DESC) AS latest_appointment_rank
           FROM (appointment a
             JOIN supplier_assessment_appointment sa ON ((a.id = sa.appointment_id)))
        )
 SELECT r.id,
    r.sent_at,
    r.sent_by_id AS sent_by,
    r.reference_number,
    r.service_usercrn AS crn,
    r.created_by_id AS created_by,
    au.id AS assigned_to_id,
    au.auth_source AS assigned_auth_source,
    au.user_name AS assigned_user_name,
    sau.auth_source AS sent_auth_source,
    sau.user_name AS sent_user_name,
    rs.first_name AS service_user_first_name,
    rs.last_name AS service_user_last_name,
    sp.id AS service_provider_id,
    sp.name AS service_provider_name,
    i.title AS intervention_title,
    r.concluded_at,
    rl.expected_release_date,
    rl.referral_releasing_12_weeks AS is_referral_releasing_in12weeks,
    rl.type AS referral_type,
    rl.prison_id,
    ppd.probation_office,
    ppd.pdu,
    ppd.ndelius_pdu,
    d.id AS contract_id,
    r.end_requested_at,
    es.id AS eosr_id,
    saa.id AS appointment_id
   FROM (((((((((((referral r
     LEFT JOIN cte rass ON (((rass.referral_id = r.id) AND (rass.current_assignment_rank = 1))))
     LEFT JOIN supplier_appointment saa ON (((saa.referral_id = r.id) AND (saa.latest_appointment_rank = 1))))
     LEFT JOIN referral_location rl ON ((rl.referral_id = r.id)))
     LEFT JOIN auth_user au ON ((rass.assigned_to_id = au.id)))
     LEFT JOIN auth_user sau ON ((r.sent_by_id = sau.id)))
     LEFT JOIN referral_service_user_data rs ON ((rs.referral_id = r.id)))
     LEFT JOIN intervention i ON ((r.intervention_id = i.id)))
     LEFT JOIN dynamic_framework_contract d ON ((d.id = i.dynamic_framework_contract_id)))
     LEFT JOIN service_provider sp ON (((sp.id)::text = (d.prime_provider_id)::text)))
     LEFT JOIN probation_practitioner_details ppd ON ((ppd.referral_id = r.id)))
     LEFT JOIN end_of_service_report es ON ((es.referral_id = r.id)));

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views