Changes between Initial Version and Version 1 of AdvancedReport17

Timestamp:

08/21/26 06:53:54 (28 hours ago)

Author:

235018

Comment:

--

AdvancedReport17

@@ +1 @@
+= Number of product changes each employee has made in the last month
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_employee_product_changes_last_month()
+RETURNS TABLE (
+    ssn BIGINT,
+    employee_name TEXT,
+    number_of_product_changes BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        e.ssn,
+        p.name AS employee_name,
+        COUNT(DISTINCT mc.date_and_time) AS number_of_product_changes
+    FROM employees e
+    JOIN personal p
+        ON e.ssn = p.ssn
+    LEFT JOIN makes_change mc
+        ON e.ssn = mc.ssn
+    LEFT JOIN change ch
+        ON mc.date_and_time = ch.date_and_time
+    WHERE
+        mc.date_and_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
+        AND mc.date_and_time < date_trunc('month', CURRENT_DATE)
+    GROUP BY
+        e.ssn,
+        p.name
+    ORDER BY
+        number_of_product_changes DESC;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- E(id, date_of_hire)
+- P(id, name, first_name, last_name, email, password, permissions, type, authorisation)
+- MC(id, date_and_time, permission, type, authorisation)
+- CH(date_and_time, product_code, changes_made)
+
+**JOIN employees with their personal information:**
+- J1 ← E ⨝E.SSN = P.SSN P
+
+**JOIN employees with the changes they have made:**
+- J2 ← J1 ⨝E.SSN = MC.SSN MC
+
+**JOIN the changes with the corresponding product:**
+- J3 ← J2 ⨝MC.date_and_time = CH.date_and_time CH
+
+**SELECT only changes made during the last calendar month:**
+- F1 ← σ
+date_and_time ≥ START_OF_CURRENT_MONTH - 1 MONTH
+∧
+date_and_time < START_OF_CURRENT_MONTH
+(J3)
+
+**Calculate total number of distinct changes made by each employee:**
+- R ← γSSN, name;
+     COUNT(DISTINCT date_and_time) → number_of_product_changes
+     (F1)
+
+**Sort by total number of changes made:**
+- R_final ← τnumber_of_product_changes DESC(R)
+
+