Changes between Version 33 and Version 34 of AdvancedReports

Timestamp:

07/22/24 14:47:49 (6 months ago)

Author:

184006

Comment:

--

AdvancedReports

@@ -216 +216 @@
     number_of_users DESC;
 }}}
-== Kолку пари се трошат за платите на вработените во секој ветеринарен центар.
-{{{
-   SELECT
-    vc.name AS vet_center_name,
-    SUM(CASE WHEN j.predictedSalery LIKE '%EUR%' THEN CAST(SUBSTRING(j.predictedSalery, 1, POSITION(' ' IN j.predictedSalery) - 1) AS DECIMAL) ELSE 0 END) AS total_salary_expense
-FROM
-    project.vet_centers vc
-JOIN
-    project.jobs j ON vc.id = j.vetCentersID
-LEFT JOIN
-    project.users u ON vc.id = u.jobs_id
-GROUP BY
-    vc.id, vet_center_name
-ORDER BY
-   total_salary_expense DESC;
+== За секој ветеринарен центар, колку пари одвојува за нови вработени и која е просечната плата по вработен која се нуди.
+{{{
+WITH SalaryData AS (
+    SELECT
+        v.id AS vet_center_id,
+        v.name AS vet_center_name,
+        j.predictedsalery AS salary
+    FROM
+        vet_centers v
+    JOIN
+        jobs j ON v.id = j.vetCentersID
+),
+SalaryAggregates AS (
+    SELECT
+        vet_center_id,
+        vet_center_name,
+        COUNT(salary) AS number_of_employees,
+        SUM(CAST(REPLACE(salary, ' EUR', '') AS DECIMAL)) AS total_salary_expenses,
+        AVG(CAST(REPLACE(salary, ' EUR', '') AS DECIMAL)) AS average_salary
+    FROM
+        SalaryData
+    GROUP BY
+        vet_center_id, vet_center_name
+)
+
+SELECT
+    sa.vet_center_name,
+    sa.number_of_employees,
+    sa.total_salary_expenses,
+    sa.average_salary
+FROM
+    SalaryAggregates sa
+ORDER BY
+    sa.total_salary_expenses DESC;
 }}}
 == Прикажување на ветеринарни центри кои што корисникот може да ги посети доколку живее во Охрид.
@@ -324 +343 @@
   avg_sales_quantity DESC;
 }}}
-== Прикажи го продуктот за кој постојат најмал број направени нарачки.
-{{{
-
-WITH product_orders_count AS (
-    SELECT
-        p.id AS product_id,
-        p.name AS product_name,
-        p.description AS product_description,
-        p.price AS product_price,
-        COALESCE(COUNT(o.id), 0) AS orders_count
-    FROM
-        project.products p
-    LEFT JOIN
-        project.product_are_made_orders pamo ON p.id = pamo.id_products
-    LEFT JOIN
-        project.orders o ON pamo.id_orders = o.id
-    GROUP BY
-        p.id, p.name, p.description, p.price
-)
-SELECT
-    product_id,
-    product_name,
-    product_description,
-    product_price,
-    orders_count
-FROM
-    product_orders_count
-WHERE
-    orders_count = (
-        SELECT MIN(orders_count)
-        FROM product_orders_count
-    );
-
-}}}