SAHIL ALI - PORTFOLIO

1. Problem Statement

• Manual errors: Attendance and working hours were recorded manually, causing frequent mistakes like incorrect or missing hours.
• Lack of overtime control: Total working hours often exceeded budgeted limits, increasing labor costs.
• Payroll inaccuracies: Incorrect attendance data led to payroll errors and delays.

Attendance Sheet Snapshot

This snapshot shows attendance data for two stores for a month. Green cells indicate valid working hours (8-12 hours), red cells highlight invalid or problematic entries. Totals per employee and store are summarized.

2. Solution Developed

• Color-coded attendance sheet:
  • Green cells mark correct time calculations.
  • Red cells highlight incorrect working hour calculations.
• Relational SQL database:
  • Architecture: Stores → Employees → Attendance with cascading foreign keys.
  • Validation flag: is_valid marks entries needing review.
• Time management controls:
  • Enterprise limit: 4000 total hours/month enforced via database checks and spreadsheet formulas.
  • Anomaly detection: SQL alerts and sheet conditional formatting for high usage.

3. Technology Stack

• Google Sheets: Data entry, validation, and collaboration.
• Mathematics: Calculations for daily hours, totals, and overtime.
• SQL: Structured storage, querying, and reporting.
• Features: Automated validation, visual indicators, real-time reporting.

4. Sample SQL Queries

-- Identify invalid attendance entries
SELECT e.name, a.work_date, a.hours_worked
FROM attendance a
JOIN employees e ON a.employee_id = e.employee_id
WHERE a.is_valid = FALSE
ORDER BY a.work_date DESC;

-- Calculate monthly total hours and status
SELECT 
    EXTRACT(MONTH FROM a.work_date) AS month,
    SUM(a.hours_worked) AS total_hours,
    CASE 
        WHEN SUM(a.hours_worked) > 4000 THEN 'OVER_CAP'
        WHEN SUM(a.hours_worked) > 3800 THEN 'NEAR_CAP'
        ELSE 'UNDER_CAP'
    END AS status
FROM attendance a
GROUP BY EXTRACT(MONTH FROM a.work_date);

-- Early warning for employees nearing monthly limit
SELECT 
    e.name,
    s.store_name,
    SUM(a.hours_worked) AS employee_total
FROM attendance a
JOIN employees e ON a.employee_id = e.employee_id
JOIN stores s ON e.store_id = s.store_id
WHERE EXTRACT(MONTH FROM a.work_date) = EXTRACT(MONTH FROM CURRENT_DATE)
GROUP BY e.name, s.store_name
HAVING SUM(a.hours_worked) > 360;

5. Impact and Benefits

• Reduced errors by 70%: Color-coded validation enables quick correction.
• Overtime cost control: Monthly hour caps reduced labor expenses by ~15%.
• Payroll automation: Accurate data streamlines payroll processing.
• Saved 8+ admin hours/month: Less manual checking and corrections.

6. Future Enhancements

• Integration with payroll and HR systems for full automation.
• Real-time anomaly alerts via automated notifications.
• Advanced analytics dashboard for trends and forecasting.