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

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

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

SELECT 
    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(cov.cov_type, ', ') AS Covered_Services
FROM 
    project.Customer c
JOIN 
    project.Pol_dog pd ON c.c_id = pd.c_id
JOIN 
    project.Policy p ON pd.policy = p.p_id
JOIN 
    project.Package pkg ON p.package = pkg.code
LEFT JOIN 
    project.Covers cov ON pkg.code = cov.package
GROUP BY 
    c.name, c.email, p.p_id, p.sdate, p.edate, pkg.title, pkg.total
ORDER BY 
    Customer_Name;

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

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

SELECT 
    pkg.type_pol AS Policy_Type,
    COUNT(DISTINCT pd.c_id) AS Total_Customers,
    COUNT(DISTINCT p.p_id) AS Total_Policies,
    SUM(pay.p_amount) AS Total_Revenue
FROM 
    project.Package pkg
JOIN 
    project.Policy p ON pkg.code = p.package
LEFT JOIN 
    project.Pol_dog pd ON p.p_id = pd.policy
LEFT JOIN 
    project.Payment pay ON p.p_id = pay.policy
GROUP BY 
    pkg.type_pol
ORDER BY 
    Total_Revenue DESC, Total_Customers DESC;