Changes between Initial Version and Version 1 of View3

Timestamp:

08/21/26 08:43:34 (26 hours ago)

Author:

235018

Comment:

--

View3

@@ +1 @@
+= Monthly sales and profit per store
+
+=== Description
+This view provides a monthly overview of sales and profit for each store by combining information from the REPORT, STORE, and exchanges_data tables.
+
+The view is intended for:
+
+- monitoring monthly store performance
+- financial reports
+- comparing stores
+- analyzing sales and profit trends
+
+==== Tables covered by the view:
+
+- Report
+- Store
+- exchanges_data
+
+==== SQL код
+{{{#!sql
+CREATE OR REPLACE VIEW vw_monthly_sales_profit AS
+SELECT
+    s.store_id,
+    s.name AS store_name,
+    r.date,
+    r.month_and_year,
+    r.profit,
+    r.overall_profit,
+    ed.monthly_profit,
+    ed.sales,
+    ed.damages
+
+FROM store s
+JOIN report r
+    ON r.store_id = s.store_id
+LEFT JOIN exchanges_data ed
+    ON ed.store_id = r.store_id
+    AND ed.date = r.date;
+
+}}}
+
+==== Logic explanation
+**1.** The store information is retrieved from `store`.
+**2.** The `report` table provides the monthly profit and overall profit.
+**3.** The `exchanges_data` table provides monthly profit, sales, and damages.
+**4.** LEFT JOIN is used for `exchanges_data` so that a report is still displayed even if no corresponding data exchange record exists.
+**5.** Each row represents the financial information for a particular store and reporting date.
+
+==== Reason for view 
+This view is useful because:
+
+- It centralizes monthly financial information
+- It simplifies financial reporting queries
+- It allows easy comparison of store performance
+- It provides sales, profit, and damages in one result
+- It reduces repeated JOIN operations in the application
+
+Without this view, when doing any analitical tasks system will have to make multiple joints to get the results.