Write a SQL Query to Generate an Employee Payroll Report with Overtime Calculations

Generating payroll reports is a common use case in HR and payroll management systems. In this tutorial, we will demonstrate how to write an SQL query to calculate an employee’s payroll, including basic salary, overtime pay, and deductions, and present the data in a detailed report format.

Scenario Description

Imagine a table named employee_salaries with the following structure:

Query

The query below calculates the total salary by including overtime pay and deducting the specified deductions:

sqlCopy codeSELECT  
    employee_id,  
    name,  
    basic_salary,  
    hra,  
    da,  
    (overtime_hours * overtime_rate) AS overtime_pay,  
    (basic_salary + hra + da + (overtime_hours * overtime_rate) - deductions) AS net_salary  
FROM  
    employee_salaries  
ORDER BY  
    net_salary DESC;  

Explanation

1. Columns:
   • overtime_pay is calculated as the product of overtime_hours and overtime_rate.
   • net_salary is computed by adding the basic salary, HRA, DA, and overtime pay, then subtracting deductions.
2. Sorting:
   • The result is ordered by net_salary in descending order, so the highest earners appear first.

Output

Assuming the following data in the employee_salaries table:

The output would be:

Conclusion

This SQL query provides a complete payroll report by including overtime calculations. It’s efficient and can be modified to suit additional payroll requirements. Add this to your SQL toolkit to make payroll management easier!

• SQL Query to Remove Duplicate Records While Keeping Latest Entry
• Find Employees Without Training Using SQL LEFT JOIN
• SQL Query to Find Top 3 Salaries Without LIMIT or TOP
• SQL Query to Calculate Running Total Without Window Functions
• SQL Query to Find Duplicate Records with Different Values