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

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

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

SELECT 
    TO_CHAR(p.sdate, 'YYYY-MM') AS Month_Year,
    c.name AS Customer_Name,
    c.email AS Customer_Email,
    p.p_id AS Policy_ID,
    p.sdate AS Start_Date,
    p.edate AS End_Date,
    CASE 
        WHEN p.edate > CURRENT_DATE THEN 'Active'
        ELSE 'Expired'
    END AS Policy_Status,
    pkg.title AS Package_Title,
    pkg.total AS Package_Total,
    STRING_AGG(DISTINCT cov.cov_type, ', ') AS Covered_Services
FROM 
    project.Customer AS c
JOIN 
    project.Pol_dog AS pd ON c.c_id = pd.c_id
JOIN 
    project.Policy AS p ON pd.policy = p.p_id
JOIN 
    project.Package AS pkg ON p.package = pkg.code
LEFT JOIN 
    project.Covers AS cov ON pkg.code = cov.package
GROUP BY 
    Month_Year, c.name, c.email, p.p_id, p.sdate, p.edate, pkg.title, pkg.total
ORDER BY 
    Month_Year DESC, Customer_Name;

Извештај 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;