Changes between Version 3 and Version 4 of AdvancedReports2

Timestamp:

04/29/25 13:00:20 (12 days ago)

Author:

175012

Comment:

--

AdvancedReports2

@@ -39 +39 @@
 === Извештај 2: Приходи од полиси по тип и број на клиенти ===
 
-Овој извештај ги прикажува приходите генерирани од полиси поделени по тип на пакет и го вклучува бројот на клиенти кои го користат секој тип на пакет за секој месец посебно.
+Овој прашалник прикажува годишен извештај по тип на полиси. За секоја година и секој тип на полиса (Автомобилска, Патничка, Имотна)
 
 {{{#!sql
 
+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 
-    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;
-
+    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;