ReportSQL - Assignments

When Reports - Assignments just won't get you where you need to go. Sometimes needed so you don't prevent cases from showing up because you would otherwise be filtering on assignments.

Assigned To name as a clickable link to the user profile instead of the client profile

• Case Data > Additional Assignments > Assigned To > Person > Full Name (Last, First) gives you a nice clickable name, but the link points to the client profile.
• Text formula field on Case Data > Additional Assignments > Assigned To
• Does not provide a user-style filter, so hide Full Name (Last, First) and use that as your filter.
• Don't forget to Format the column as Unescaped (Raw HTML).

concat(
'<a href="/user/dynamic_profile/view/' || %tableAlias%.id || '">', 
(SELECT 
CONCAT_WS(', ', 
CONCAT_WS(' ', last, suffix), 
CONCAT_WS(' ', first, middle))
from person
WHERE person.id = %tableAlias%.person_id),
'</a>')

Investigator name (an assignment type) without needing to Text Join all other assignments

• Text formula field on Case Data.
• Does not provide a user-based filter
• Shows most recent assignment if more than one
• The 3294880 is the ID of the desired assignment type

(select concat(last, ', ', first) from person left join users on person_id = person.id where users.id = (select distinct on (module_id) assigned_to from assignment where module_id = %tableAlias%.id and assignment_type = 3294880 and date_end is null order by module_id, date_start DESC))

Has a Social Worker (or other type) ever been assigned to a case

• Boolean formula field on Case Data
• assignment_type is the lookup ID number.

case when (select count(*) from assignment
where module = 'matter' and module_id = %tableAlias%.id
and assignment_type = 3294881)
> 0
then true
else
false
end

First Primary Advocate on a Re-Assigned Case

• Text formula field on Case Data.
• Does not provide a user-based filter.

(select concat(last, ', ', first) as first_primary
from person
left join users on person_id = person.id
where users.id = (select distinct on (module_id) assigned_to from assignment where module_id = %tableAlias%.id and assignment_type = (select id from lookup_assignment_types where note = 'primary')
order by module_id, date_start))

Count of Currently Assigned Attorneys (to find unsupervised cases)

• Only lightly tested at this point
• Numeric formula field on Case Data
• "Attorneys" is defined by the hardcoded IDs from the Advocate Type lookup
• Filter for "Less than 1" to get cases with no "attorney" currently assigned

(SELECT count("assignment".id) FROM matter
JOIN "assignment" ON module_id = matter.id 
JOIN users ON "assignment".assigned_to = users.id
JOIN user_types ON user_types.user_id = users.id 
WHERE matter.id = %tableAlias%.id
AND "assignment"."module" = 'matter'
AND "assignment".date_end IS NULL 
AND user_types.type IN (506, 3318810, 3318831, 3318836))

Case has a specific type of assignment

• Old style implicit JOINs. Use with care.
• Boolean formula field on Case Data

%tableAlias%.id in (select module_id from assignment, lookup_assignment_types where lookup_assignment_types.note = 'ftu' and assignment.assignment_type = lookup_assignment_types.id and module = 'matter')

Case has one type of assignment but not another

• Old style implicit JOINs. Use with care.
• Boolean formula field on Case Data
• lookup_assignment_types.note is a system text value you can only see through Batch Edit on a given lookup.

%tableAlias%.id in 
(select module_id from assignment, lookup_assignment_types where lookup_assignment_types.note = 'ftu' and assignment.assignment_type = lookup_assignment_types.id and module = 'matter') 
and
%tableAlias%.id not in 
(select module_id from assignment, lookup_assignment_types where lookup_assignment_types.name = 'Case Handler' and assignment.assignment_type = lookup_assignment_types.id and module = 'matter')