Skip to content

README.md

Latest commit

 

History

History
75 lines (56 loc) · 2.16 KB

README.md

File metadata and controls

75 lines (56 loc) · 2.16 KB

Employee Attendance & Salary Analytics System

A MySQL-based system for tracking employee attendance, calculating working hours, overtime, and generating monthly payroll reports.

Overview

This system manages employee attendance records and automatically calculates:

  • Daily hours worked
  • Late arrivals (after 10:00 AM)
  • Overtime hours (hours beyond 8 per day)
  • Monthly payroll with overtime compensation

Features

  • Attendance Tracking: Daily check-in/check-out records
  • Automated Calculations: Hours worked, late days, and overtime computed automatically
  • Monthly Summaries: Aggregated statistics per employee per month
  • Payroll Generation: Stored procedure to generate monthly payroll reports
  • Analytics Views: Pre-built views for daily metrics, monthly summaries, and payroll previews

Database Structure

  • departments: Department information
  • employees: Employee details with base salary and overtime rates
  • attendance: Daily check-in/check-out records
  • payroll_monthly: Generated monthly payroll snapshots

Setup

  1. Ensure MySQL 8+ is installed and running
  2. Execute the SQL files in order: 
    01_schema.sql      - Creates database and tables
02_seed_data.sql   - Inserts sample data
03_views.sql       - Creates analytical views
04_procedures.sql  - Creates payroll generation procedure
05_sample_queries.sql - Example queries
  3. (Optional) Run 99_reset.sql to drop and recreate the database

Usage

Generate Monthly Payroll

CALL sp_generate_payroll('2025-10');

View Daily Attendance Metrics

SELECT * FROM v_daily_work;

View Monthly Summary

SELECT * FROM v_monthly_summary WHERE month_year = '2025-10';

View Payroll Preview

SELECT * FROM v_payroll_preview WHERE month_year = '2025-10';

Business Rules

  • Standard work hours: 8 hours per day
  • Late cutoff: 10:00 AM (hour 10 or later)
  • Overtime: Hours worked beyond 8 hours per day
  • Salary calculation: Base salary + (overtime hours × overtime rate)

Technology

  • MySQL 8+
  • SQL Views
  • Stored Procedures