Changes between Initial Version and Version 1 of View4

Timestamp:

08/21/26 08:48:20 (26 hours ago)

Author:

235018

Comment:

--

View4

@@ +1 @@
+= Employee workload and salary report
+
+=== Description
+This view provides an overview of employee working hours, wages, payment methods, and the stores where employees work.
+
+The view is intended for:
+
+- monitoring employee workload
+- calculating and reviewing employee wages
+- payroll reports
+- analyzing employee work across stores
+
+==== Tables covered by the view:
+
+- Personal
+- Employees
+- Store
+- works_in_store
+- worked
+
+==== SQL код
+{{{#!sql
+CREATE OR REPLACE VIEW vw_employee_workload_salary AS
+SELECT
+    e.id AS employee_id,
+    p.first_name,
+    p.last_name,
+    p.email,
+    e.date_of_hire,
+
+    s.store_id,
+    s.name AS store_name,
+
+    w.week,
+    w.total_week,
+    w.working_hours,
+    w.wage,
+    w.pay_method
+
+FROM employees e
+JOIN personal p
+    ON p.id = e.id
+JOIN works_in_store wis
+    ON wis.id = e.id
+JOIN store s
+    ON s.store_id = wis.store_id
+LEFT JOIN worked w
+    ON w.id = e.id
+    AND w.store_id = wis.store_id;
+
+}}}
+
+==== Logic explanation
+**1.** The `employees` table identifies personnel who are employees.
+**2.** The `personal` table provides their personal information.
+**3.** The `works_in_store` relationship identifies the store where each employee works.
+**4.** The `store` table provides the store name and identifier.
+**5.** The `worked` relationship provides working hours, weekly totals, wage, and payment method.
+**6.** LEFT JOIN is used for `worked` so that an employee can still appear even if no work-report record has yet been entered.
+
+==== Reason for view 
+This view is useful because:
+
+- It combines employee, store, working-hour, and wage information
+- It simplifies payroll-related queries
+- It allows managers to monitor employee workload
+- It provides a consistent source for employee work reports
+- It avoids repeatedly joining five different tables
+
+Without this view employers have to manually track employees workload and payroll, using many JOINTs on multiple occasions and with that overloading the system.