Changes between Version 2 and Version 3 of AdvancedReports2

Timestamp:

04/29/25 12:55:14 (12 days ago)

Author:

175012

Comment:

--

AdvancedReports2

@@ -9 +9 @@
 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
+    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.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
+    project.Policy p
 LEFT JOIN 
-    project.Covers AS cov ON pkg.code = cov.package
+    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, c.name, c.email, p.p_id, p.sdate, p.edate, pkg.title, pkg.total
+    Month_Year
 ORDER BY 
-    Month_Year DESC, Customer_Name;
+    Month_Year DESC;
+