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:

Column Name	Data Type	Description
employee_id	INT	Unique identifier for the employee
name	VARCHAR(50)	Employee name
basic_salary	DECIMAL(10,2)	The base salary for the employee
hra	DECIMAL(10,2)	House Rent Allowance
da	DECIMAL(10,2)	Dearness Allowance
overtime_hours	INT	Total overtime hours worked in the month
overtime_rate	DECIMAL(10,2)	Hourly rate for overtime
deductions	DECIMAL(10,2)	Total deductions for the month

---

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:

employee_id	name	basic_salary	hra	da	overtime_hours	overtime_rate	deductions
1	John Doe	50000.00	10000	5000	10	200.00	3000.00
2	Jane Doe	45000.00	9000	4000	15	250.00	4000.00

The output would be:

employee_id	name	basic_salary	hra	da	overtime_pay	net_salary
1	John Doe	50000.00	10000	5000	2000.00	65000.00
2	Jane Doe	45000.00	9000	4000	3750.00	61750.00

---

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!

• Data Lake vs Data Warehouse: Understanding the Key Differences
• Write a SQL Query to Generate an Employee Payroll Report with Overtime Calculations
• Advanced SQL Join Concepts: CROSS APPLY & OUTER APPLY
• Common SQL Mistakes & How to Fix Them Quickly
• Handling NULL Values in SQL Joins