Getting rates for life insurance deductions

Life insurance deductions are not stored in pr_deduction_ben or pr_ins_premium

• All versions of Munis

• SSRS reports

• Crystal reports

• SaaS, Tyler-hosted clients

• OnPrem, Self-hosted clients

• Employee Deductions

Life insurance uses calculations based on the ages of the employee and their dependents

The rate will be found in pr_life_insurance depending on dp_depend_DOB or  e_date_of_birth from pr_employee_mast and db_life_table from pr_deduction_ben

The following query will provide the rate for a spouse life insurance:

SELECT DISTINCT 
lf_rate_per_thousand 
, pr_deduction_ben.a_employee_number
, a_deduction_code
, DATEDIFF(YEAR , dp_depend_DOB, GETDATE()) AS SpouseAge
, d_emp_ins_amt * lf_rate_per_thousand  /1000 AS SpouseRatePerThousand
FROM pr_deduction_ben  
JOIN pr_employee_mast
ON pr_deduction_ben.a_employee_number = pr_employee_mast.a_employee_number 
JOIN pr_life_insurance 
ON db_life_table = lf_table 
AND  DATEDIFF(YEAR , dp_depend_DOB, GETDATE())>= lf_age_from
AND  DATEDIFF(YEAR , dp_depend_DOB, GETDATE()) <= lf_age_to 
WHERE dp_depend_rltn_cd ='S'
and YEAR(dp_depend_stop_dt) =9999