Напредни извештаи од базата (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: Приходи од полиси по тип и број на клиенти

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

SELECT 
    DATE_TRUNC('month', pay.p_date) AS Payment_Month,
    pkg.title AS Package_Title,
    pkg.type_pol AS Policy_Type,
    COUNT(pay.payment_num) AS Total_Payments,
    SUM(pay.p_amount) AS Total_Revenue,
    AVG(pay.p_amount) AS Average_Payment
FROM 
    project.Package pkg
JOIN 
    project.Policy p ON pkg.code = p.package
JOIN 
    project.Payment pay ON p.p_id = pay.policy
GROUP BY 
    Payment_Month, pkg.title, pkg.type_pol
ORDER BY 
    Payment_Month DESC, Total_Revenue DESC;