Changes between Initial Version and Version 1 of AdvancedReport19

Timestamp:

08/21/26 07:03:41 (28 hours ago)

Author:

235018

Comment:

--

AdvancedReport19

@@ +1 @@
+= List of reports which haven't been approved
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_unapproved_reports()
+RETURNS TABLE (
+    report_date DATE,
+    store_id INT,
+    overall_profit NUMERIC,
+    month_and_year TEXT,
+    profit NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        r.date AS report_date,
+        r.store_id,
+        r.overall_profit,
+        r.month_and_year,
+        r.profit
+    FROM report r
+    LEFT JOIN approves a
+        ON r.date = a.date
+        AND r.store_id = a.store_id
+    WHERE a.date IS NULL
+    ORDER BY
+        r.date DESC;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- R(date, store_ID, overall_profit, monthly_profit, month_and_year, profit, sales_trend, marketing_growth, owner_signature)
+- A(date, id)
+
+**JOIN approves with store owner:**
+- J1 ← R ⟕R.date = A.date A
+
+**FILTER out all the reports for which no corresponding approval exists:**
+- F1 ← σSSN IS NULL(J1)
+
+**Sort by report date:**
+- R_final ← τdate DESC(F1)
+
+