Skip to main content
Skip table of contents

SQL - Find Owners That Should Be in Due Diligence

Description

SQL used to find owners within a certain state and date range that should be in Due Diligence

Usage

Update State and Production date as necessary to find which owners should be in Due Diligence

SQL

CODE
SELECT
	oa.owner_number,
	MIN(oa.production_date) AS 'Prod Date',
	oa.status,
	sum(oa.net),
	COALESCE(es. [name], r. [state])
FROM
	owner_amounts oa
	JOIN roles r ON r.anchor_id = oa.role_anchor_id
	JOIN escheat_states es ON es.id = r.escheat_state_id
	JOIN business_associates ba ON ba.id = r.business_associate_id
WHERE
	oa.active = 1
	AND COALESCE(es. [name], r. [state]) = 'TX'		-- Limited to a single state
	AND ba.active = 1
	AND oa.company_code_id = 4
	AND oa.upstream_suspense_code_id in(
		SELECT
			sc.id FROM escheat_rules er
			JOIN escheatable_suspense_codes esc ON esc.escheat_rule_id = er.id
			JOIN escheat_states es ON es.id = er.escheat_state_id
			JOIN upstream_suspense_codes sc ON sc.id = esc.upstream_suspense_code_id
		WHERE
			es. [name] = 'TX')						-- Limited to a single state
GROUP BY
	oa.owner_number, oa.status, es. [name], r. [state]
HAVING
	min(oa.production_date) <= '2018-03-01'			-- Selected Production Date
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.