Напредни извештаи од базата (SQL и складирани процедури)

Извештај 1: Листа на клиенти со сите активни полиси и деталите за нив

Овој извештај ќе ги прикаже сите клиенти со нивните активни полиси, типот на полиса, пакетот што го користат, и сите покриени услуги посебно за секој месец.

SELECT 
    TO_CHAR(p.sdate, 'YYYY-MM') AS Month_Year,
    COUNT(p.p_id) AS Total_Policies,
    COUNT(DISTINCT CASE WHEN ap.a_id IS NOT NULL THEN p.p_id END) AS Auto_Policies,
    COUNT(DISTINCT CASE WHEN tp.tr_id IS NOT NULL THEN p.p_id END) AS Travel_Policies,
    COUNT(DISTINCT CASE WHEN pp.pr_id IS NOT NULL THEN p.p_id END) AS Property_Policies,
    SUM(pkg.total) AS Total_Insured_Value,
    SUM(pay.p_amount) AS Total_Revenue
FROM 
    project.Policy p
LEFT JOIN 
    project.auto_pol ap ON p.p_id = ap.pol_id
LEFT JOIN 
    project.Travel_pol tp ON p.p_id = tp.pol_id
LEFT JOIN 
    project.Property_pol pp ON p.p_id = pp.pol_id
LEFT JOIN 
    project.Package pkg ON p.package = pkg.code
LEFT JOIN 
    project.Payment pay ON p.p_id = pay.policy
GROUP BY 
    Month_Year
ORDER BY 
    Month_Year DESC;

Извештај 2: Приходи од полиси по тип и број на клиенти

Овој прашалник прикажува годишен извештај по тип на полиси. За секоја година и секој тип на полиса (Автомобилска, Патничка, Имотна)

WITH policy_type_map AS (
    SELECT 
        p.p_id,
        EXTRACT(YEAR FROM p.sdate) AS policy_year,
        CASE 
            WHEN ap.a_id IS NOT NULL THEN 'Auto'
            WHEN tp.tr_id IS NOT NULL THEN 'Travel'
            WHEN pp.pr_id IS NOT NULL THEN 'Property'
            ELSE 'Other'
        END AS policy_type,
        pkg.total AS insured_value
    FROM 
        project.Policy p
    LEFT JOIN project.auto_pol ap ON p.p_id = ap.pol_id
    LEFT JOIN project.Travel_pol tp ON p.p_id = tp.pol_id
    LEFT JOIN project.Property_pol pp ON p.p_id = pp.pol_id
    LEFT JOIN project.Package pkg ON p.package = pkg.code
),
payments_per_policy AS (
    SELECT 
        policy,
        SUM(p_amount) AS total_payment
    FROM project.Payment
    GROUP BY policy
),
combined AS (
    SELECT 
        ptm.policy_year,
        ptm.policy_type,
        ptm.p_id,
        ptm.insured_value,
        COALESCE(pp.total_payment, 0) AS payment
    FROM policy_type_map ptm
    LEFT JOIN payments_per_policy pp ON ptm.p_id = pp.policy
),
final_aggregation AS (
    SELECT 
        policy_year,
        policy_type,
        COUNT(p_id) AS total_policies,
        SUM(insured_value) AS total_insured_value,
        SUM(payment) AS total_revenue,
        ROUND(AVG(payment), 2) AS avg_revenue_per_policy
    FROM combined
    GROUP BY policy_year, policy_type
)
SELECT 
    policy_year,
    policy_type,
    total_policies,
    total_insured_value,
    total_revenue,
    avg_revenue_per_policy
FROM final_aggregation
ORDER BY policy_year DESC, policy_type;