Skip to main content

Curated SQL Editor Templates

  • Updated

Below are a selection of curated SQL query templates you can use as a starting point for building your reports.  Some of these queries will need to be modified to match the names of your products.

Simple Policies in Force (PIF)

-- pick ONE address per policy (here: the first alphabetically)
SELECT
    t.state,
    t.policy_type,
    r.policy_number,
    MIN(l.full_address) AS full_address
FROM m_inforce_policies  AS i
JOIN v_policy_types      AS t  ON t.policy_type_id = i.policy_type_id
JOIN v_revisions         AS r  ON r.revision_id    = i.revision_id
JOIN v_properties        AS l  ON l.revision_id    = i.revision_id
GROUP BY
    t.state,
    t.policy_type,
    r.policy_number;

Policy In Force with Builder Info

select
 t.state,
 t.policy_type,
 r.policy_number,
 l.full_address,
 max(case c.item_name 
     when 'Property Characteristics' and c.builder_obj like '%categories%' 
     then json_unquote(json_extract(c.builder_obj, '$.categories."Construction Type"'))
     else '' end) as `Construction Type`,
 sum(case c.item_name when 'Coverage A - Dwelling' then c.coverage_limit end) as `Cov A Limit`,
 sum(case c.item_name when 'Coverage B - Other Structures' then c.coverage_limit end) as `Cov B Limit`
from m_inforce_policies i
join v_policy_types t on t.policy_type_id = i.policy_type_id
join v_revisions r on r.revision_id = i.revision_id
join v_properties l on l.revision_id = i.revision_id
join v_property_items c on c.property_id = l.property_id
 and c.item_name in ('Property Characteristics', 'Coverage A - Dwelling', 'Coverage B - Other Structures')
group by r.policy_number;

This report will not extract any data until the property categories have been matched to your lines/products.  This serves as an example of extracting data out of the BuilderObject using the json_unquote mechanism.

Pattern for Accessing system tags 

Tags are represented in JSON objects in the database.  Tags are present in at least the following views: v_claims, v_contacts, v_policy_type, v_insureds, v_revision. Standardized tags such as ACORD tabs are used to understand what types of lines for a specific customer represent which standard types of lines, and can make a stock report able to handle custom information.

If tags are needed for a report but improperly or not applied the report will not provide accurate data.

SELECT * FROM v_policy_types
WHERE 1
AND policy_type_system_tags->>'$.\"NxTech AL3 Codes\"' = "BA"

Premium Earned to Loss Incurred

Header page pattern

SELECT Field, Value FROM (
    SELECT 'Report Name' AS Field, 'Premium Earned to Loss Incurred'  AS value
    UNION
    SELECT 'Company' AS Field, contact_name AS value FROM (
      SELECT contact_name FROM v_contacts
      WHERE roles like '%Primary Carrier%'
      LIMIT 1
    ) c
    UNION
    SELECT 'Run Date' AS Field, CURDATE() AS value
    UNION
    SELECT 'Date Range' AS Field, CONCAT('<<$StartDate>>', ' - ', '<<$EndDate>>') AS value
) AS report;

This is how to make a header tab with run information.  Create additional tabs in an Excel format file for the contents of the report. 

Pattern for Listing and aggregating columns: Total By Agency

WITH
  details AS (
  SELECT 
      u.agency_name AS "Agency",
      sum(incurred_losses) AS "Loss Incurred",
      sum(incurred_losses - incurred_ss) AS "Loss Less Salv Sub",
      sum(incurred_legal) AS "Legal Losses Incurred",
      sum(incurred_adjusting) AS "Adjusted Losses Incurred",
      sum(incurred_losses + incurred_legal + incurred_adjusting) AS "Total Losses Incurred",
      sum(earned_premium) AS "Earned Premium",
      CASE 
          WHEN SUM(earned_premium) = 0 OR SUM(incurred_losses) = 0 THEN 0 
          ELSE SUM(incurred_losses) / SUM(earned_premium) 
      END AS "Ratio (-LAE)",
  
      CASE 
          WHEN SUM(earned_premium) = 0 OR SUM(incurred_losses + incurred_legal + incurred_adjusting) = 0 THEN 0 
          ELSE SUM(incurred_losses + incurred_legal + incurred_adjusting) / SUM(earned_premium) 
      END AS "Ratio (+LAE)",
  
      CASE 
          WHEN SUM(earned_premium) = 0 OR SUM(incurred_losses + incurred_legal + incurred_adjusting + incurred_ss) = 0 THEN 0 
          ELSE SUM(incurred_losses + incurred_legal + incurred_adjusting + incurred_ss) / SUM(earned_premium) 
      END AS "Ratio (+ LAE and Salv Sub)"
  FROM 
  (SELECT 
          vra.agency_name,
          SUM(lir.loss) as incurred_losses,
          SUM(lir.legal) AS incurred_legal,
          SUM(lir.adjusting) AS incurred_adjusting,
          SUM(lir.salvage_subrogation) AS incurred_ss,
          0 earned_premium
      FROM 
        (SELECT 
           li.claim_id,
           SUM(li.loss_reserved + li.loss_paid) as loss,
           SUM(li.legal_reserved + li.legal_paid) AS legal,
           SUM(li.adjusting_reserved + li.adjusting_paid) AS adjusting,
           0 salvage_subrogation
         FROM v_losses_incurred li
         WHERE CAST(li.date_incurred AS DATE) BETWEEN '<<$StartDate>>' AND '<<$EndDate>>'
         GROUP BY 1
        UNION ALL
        SELECT
           r.claim_id, 0, 0, 0,
           sum(r.salvage_reserved + r.salvage_received + r.subrogation_reserved + r.subrogation_received)
        FROM v_recoveries r
        WHERE CAST(r.date_incurred AS DATE) BETWEEN '<<$StartDate>>' AND '<<$EndDate>>'
        GROUP BY 1
      ) lir
      JOIN v_claims c ON c.claim_id = lir.claim_id
      JOIN v_revisions_agencies vra on vra.revision_id = c.revision_id
      GROUP BY 1
  UNION ALL
      SELECT 
          a.agency_name, 0, 0, 0, 0,
          SUM(mp.end_earned - mp.prior_earned)
      FROM m_premium_terms mp
      JOIN v_revisions_agencies a on a.revision_id = mp.revision_id and a.agency_id = mp.agency_id
      GROUP BY 1) u
  group by 1
  )
select
    u.*
from
    (
        select
            *
        from
            details
        union all
        select
            'Totals'
            ,sum(d.`Loss Incurred`)
            ,sum(d.`Loss Less Salv Sub`)
            ,sum(d.`Legal Losses Incurred`)
            ,sum(d.`Adjusted Losses Incurred`)
            ,sum(d.`Total Losses Incurred`)
            ,sum(d.`Earned Premium`)
            ,sum(d.`Loss Incurred`)/ sum(d.`Earned Premium`) AS `Ratio (-LAE)`
            ,(sum(d.`Loss Incurred`) + sum(d.`Legal Losses Incurred`) + sum(d.`Adjusted Losses Incurred`))/ sum(d.`Earned Premium`) AS `Ratio (+LAE)`
            ,(sum(d.`Loss Less Salv Sub`) + sum(d.`Legal Losses Incurred`) + sum(d.`Adjusted Losses Incurred`))/ sum(d.`Earned Premium`) AS `Ratio (+ LAE and Salv Sub)`
        from
            details d
    ) u

Total By Policy Type

WITH
  details AS (
  SELECT 
      u.policy_type AS "Policy Type",
      sum(incurred_losses) AS "Loss Incurred",
      sum(incurred_losses - incurred_ss) AS "Loss Less Salv Sub",
      sum(incurred_legal) AS "Legal Losses Incurred",
      sum(incurred_adjusting) AS "Adjusted Losses Incurred",
      sum(incurred_losses + incurred_legal + incurred_adjusting) AS "Total Losses Incurred",
      sum(earned_premium) AS "Earned Premium",
      CASE 
          WHEN SUM(earned_premium) = 0 OR SUM(incurred_losses) = 0 THEN 0 
          ELSE SUM(incurred_losses) / SUM(earned_premium) 
      END AS "Ratio (-LAE)",
  
      CASE 
          WHEN SUM(earned_premium) = 0 OR SUM(incurred_losses + incurred_legal + incurred_adjusting) = 0 THEN 0 
          ELSE SUM(incurred_losses + incurred_legal + incurred_adjusting) / SUM(earned_premium) 
      END AS "Ratio (+LAE)",
  
      CASE 
          WHEN SUM(earned_premium) = 0 OR SUM(incurred_losses + incurred_legal + incurred_adjusting + incurred_ss) = 0 THEN 0 
          ELSE SUM(incurred_losses + incurred_legal + incurred_adjusting + incurred_ss) / SUM(earned_premium) 
      END AS "Ratio (+ LAE and Salv Sub)"
  FROM 
  (SELECT 
          pt.policy_type,
          SUM(lir.loss) as incurred_losses,
          SUM(lir.legal) AS incurred_legal,
          SUM(lir.adjusting) AS incurred_adjusting,
          SUM(lir.salvage_subrogation) AS incurred_ss,
          0 earned_premium
      FROM 
        (SELECT 
           li.claim_id,
           SUM(li.loss_reserved + li.loss_paid) as loss,
           SUM(li.legal_reserved + li.legal_paid) AS legal,
           SUM(li.adjusting_reserved + li.adjusting_paid) AS adjusting,
           0 salvage_subrogation
         FROM v_losses_incurred li
         WHERE CAST(li.date_incurred AS DATE) BETWEEN '<<$StartDate>>' AND '<<$EndDate>>'
         GROUP BY 1
        UNION ALL
        SELECT
           r.claim_id, 0, 0, 0,
           sum(r.salvage_reserved + r.salvage_received + r.subrogation_reserved + r.subrogation_received)
        FROM v_recoveries r
        WHERE CAST(r.date_incurred AS DATE) BETWEEN '<<$StartDate>>' AND '<<$EndDate>>'
        GROUP BY 1
      ) lir
      JOIN v_claims c ON c.claim_id = lir.claim_id
      JOIN v_policy_types pt ON c.policy_type_id = pt.policy_type_id
      GROUP BY pt.policy_type
  UNION ALL
      SELECT 
          pt.policy_type, 0, 0, 0, 0,
          SUM(mp.end_earned - mp.prior_earned)
      FROM m_premium_terms mp
      JOIN v_revisions v ON mp.revision_id = v.revision_id
      JOIN v_policy_types pt ON pt.policy_type_id = v.policy_type_id
      GROUP BY pt.policy_type) u
  group by u.policy_type
  order by 1 asc
  )
select
    u.*
from
    (
        select
            *
        from
            details
        union all
        select
            'Totals'
            ,sum(d.`Loss Incurred`)
            ,sum(d.`Loss Less Salv Sub`)
            ,sum(d.`Legal Losses Incurred`)
            ,sum(d.`Adjusted Losses Incurred`)
            ,sum(d.`Total Losses Incurred`)
            ,sum(d.`Earned Premium`)
            ,sum(d.`Loss Incurred`)/ sum(d.`Earned Premium`) AS `Ratio (-LAE)`
            ,(sum(d.`Loss Incurred`) + sum(d.`Legal Losses Incurred`) + sum(d.`Adjusted Losses Incurred`))/ sum(d.`Earned Premium`) AS `Ratio (+LAE)`
            ,(sum(d.`Loss Less Salv Sub`) + sum(d.`Legal Losses Incurred`) + sum(d.`Adjusted Losses Incurred`))/ sum(d.`Earned Premium`) AS `Ratio (+ LAE and Salv Sub)`
        from
            details d
    ) u

Paid Claims > 7500

SELECT
  c.claim_number,
  sum(cp.loss_paid) AS total_loss
FROM v_claims c
JOIN v_claim_payments cp ON cp.claim_id = c.claim_id
GROUP BY c.claim_number
HAVING total_loss > 7500;

YTD Premium & Dividend Transact

SELECT
  year(t.transaction_date) AS year,
  month(t.transaction_date) AS month,
  sum(t.transaction_written_premium) AS total_premium
FROM m_premium_transactions t
WHERE t.transaction_date >= DATE_FORMAT(CURDATE(), '%Y-01-01')
GROUP BY year, month;

CyberPlus Report - Inforce Limit

SELECT
  r.policy_number,
  c.item_name,
  c.coverage_limit
FROM m_inforce_policies i
JOIN v_revisions r ON r.revision_id = i.revision_id
JOIN v_policy_types t ON t.policy_type_id = i.policy_type_id
JOIN v_property_items c ON c.revision_id = i.revision_id
WHERE t.program = 'CyberPlus';

CyberPlus Report - Paid Premium

SELECT
  t.policy_number,
  t.transaction_amount,
  t.effective_date
FROM m_premium_transactions t
JOIN v_policy_types p ON p.policy_type_id = t.policy_type_id
WHERE p.program = 'CyberPlus';

Earthquake Report - Inforce Limit

SELECT
  r.policy_number,
  c.coverage_limit,
  c.item_name
FROM m_inforce_policies i
JOIN v_revisions r ON r.revision_id = i.revision_id
JOIN v_property_items c ON c.revision_id = i.revision_id
WHERE c.item_name LIKE '%Earthquake%';

Earthquake Report - Paid Premium

SELECT
  t.policy_number,
  t.transaction_amount,
  t.effective_date
FROM m_premium_transactions t
JOIN v_policy_types p ON p.policy_type_id = t.policy_type_id
WHERE p.program = 'Earthquake';

 

 
Was this article helpful?
0 out of 0 found this helpful
Return to top

Related articles