Changes between Version 52 and Version 53 of AdvancedReports

Timestamp:

08/30/24 20:34:24 (14 months ago)

Author:

184006

Comment:

--

AdvancedReports

@@ -361 +361 @@
 1. За секој ветеринарен центар да се прикажат вкупниот број на посети, вкупниот број на работни позиции за доктори и вкупниот број на посети изразени во проценти.
 '''''Резултантна шема: (vet_center_name, total_visits, total_jobs, visit_percentage)''''' 
+{{{
+WITH VetCenterVisitData AS (
+    SELECT 
+        vc.id AS vet_center_id,
+        vc.name AS vet_center_name,
+        COUNT(pc.id) AS total_visits,
+        COUNT(DISTINCT j.id) AS total_jobs
+    FROM 
+        vet_centers vc
+    LEFT JOIN 
+        pet_cares pc ON vc.id = pc.vetcentersID
+    LEFT JOIN 
+        jobs j ON vc.id = j.vetCentersID
+    GROUP BY 
+        vc.id
+),
+TotalVisitCount AS (
+    SELECT 
+        SUM(total_visits) AS total_visits_all_centers
+    FROM 
+        VetCenterVisitData
+)
+SELECT 
+    vcd.vet_center_name,
+    vcd.total_visits,
+    vcd.total_jobs,
+    ROUND((vcd.total_visits::decimal / tvc.total_visits_all_centers) * 100, 2) AS visit_percentage
+FROM 
+    VetCenterVisitData vcd,
+    TotalVisitCount tvc
+ORDER BY 
+    visit_percentage DESC;
+}}}