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 код

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.