Changes between Initial Version and Version 1 of AdvancedReport15

Timestamp:

08/21/26 06:45:28 (28 hours ago)

Author:

235018

Comment:

--

AdvancedReport15

@@ +1 @@
+= Employees ordered by total hours worked and total pay
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_employees_by_hours_and_pay()
+RETURNS TABLE (
+    ssn BIGINT,
+    employee_name TEXT,
+    total_hours_worked NUMERIC,
+    total_pay NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        e.ssn,
+        p.name AS employee_name,
+        COALESCE(SUM(w.working_hours), 0) AS total_hours_worked,
+        COALESCE(SUM(w.wage), 0) AS total_pay
+    FROM employees e
+    JOIN personal p
+        ON e.ssn = p.ssn
+    LEFT JOIN worked w
+        ON e.ssn = w.ssn
+    GROUP BY
+        e.ssn,
+        p.name
+    ORDER BY
+        total_hours_worked DESC,
+        total_pay DESC;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- E(employee_id, date_of_hire)
+- P(id, name, first_name, last_name, email, password, permissions, type, authorisation)
+- W(id, date, store_ID, week, total_week, pay_method, wage, working_hours)
+
+**JOIN employees with personal information:**
+- J1 ← E ⨝E.employee_id = P.idP
+
+**JOIN employees with work records:**
+- J2 ← J1 ⟕E.employee_id= W.id W
+
+**Calculate total hours and pay for each employee:**
+- total_hours_worked = Σ(working_hours)
+- total_pay = Σ(wage)
+- R ← γSSN, name;
+     Σ(working_hours) → total_hours_worked,
+     Σ(wage) → total_pay
+     (J2)
+
+**Sort by total hours worked and total pay:**
+- R_final ← τtotal_hours_worked DESC,
+           total_pay DESC(R)
+
+