Created
October 1, 2012 15:27
-
-
Save agustinvinao/3812501 to your computer and use it in GitHub Desktop.
Work with advisor/submitted/life/summary with advisor_submitted_premium_totals view
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
-- COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Target' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS target_premium, | |
-- COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Single' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS single_premium, | |
-- COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Excess' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS excess_premium, | |
COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Target' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS target_premium, | |
COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Single' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS single_premium, | |
COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Excess' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS excess_premium, | |
advisors.id AS entity_id, | |
TRIM(CASE WHEN people.first_name <> ' ' AND people.first_name IS NOT NULL THEN people.first_name || ' ' ELSE '' END::Text || | |
CASE WHEN people.middle_name <> ' ' AND people.middle_name IS NOT NULL THEN people.middle_name || ' ' ELSE '' END::Text || | |
CASE WHEN people.last_name <> ' ' AND people.last_name IS NOT NULL THEN people.last_name || ' ' ELSE '' END::Text || | |
CASE WHEN people.suffix <> ' ' AND people.suffix IS NOT NULL THEN people.suffix ELSE '' END::Text) AS entity_name, | |
advisors.code_name AS entity_code_name, | |
COUNT(DISTINCT policies.id) AS entity_policy_count | |
FROM | |
advisors, products, people, advisor_submitted_premium_totals, policies, submitted_premiums, policy_advisors | |
WHERE | |
advisors.organization_id = 4 AND | |
advisor_submitted_premium_totals.advisor_id = advisors.id AND | |
--submitted_premiums.policy_id = policies.id AND | |
--submitted_premiums.date >= '2011-10-01 12:00:00 -0300' AND | |
--submitted_premiums.date <= '2012-10-01 12:00:00 -0300' AND | |
advisor_submitted_premium_totals.date >= '2011-10-01 12:00:00 -0300' AND | |
advisor_submitted_premium_totals.date <= '2012-10-01 12:00:00 -0300' AND | |
policies.id = policy_advisors.policy_id AND -- advisor_submitted_premium_totals has it | |
policy_advisors.advisor_id = advisors.id AND | |
policy_advisors.is_external IS NULL AND | |
products.id = policies.product_id AND | |
products.channel_id = 7 AND | |
people.advisor_id = advisors.id | |
GROUP BY entity_id, entity_name, entity_code_name | |
ORDER BY entity_name |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment