SQL统计工作时长及月度薪资计算存储过程开发问题

Hey there! Since you're just starting out with SQL, let's walk through this problem step by step—first figuring out how to calculate monthly work hours, then wrapping that logic into a stored procedure for payroll.

First, let's assume you have a table (let's call it time_records) that tracks employee clock-ins and clock-outs, with fields like employee_id, clock_in (datetime), clock_out (datetime), and work_date (date).

The core idea is to group records by employee and month, then sum up the time between each clock-in and clock-out. Here are examples for the most common databases:

MySQL/MariaDB #

SELECT
  employee_id,
  DATE_FORMAT(work_date, '%Y-%m') AS pay_month,
  -- Calculate total hours, ignoring invalid records where clock_out is before clock_in
  SUM(CASE 
        WHEN clock_out > clock_in THEN TIMESTAMPDIFF(HOUR, clock_in, clock_out) 
        ELSE 0 
      END) AS total_work_hours
FROM time_records
GROUP BY employee_id, DATE_FORMAT(work_date, '%Y-%m');

SQL Server #

SELECT
  employee_id,
  FORMAT(work_date, 'yyyy-MM') AS pay_month,
  SUM(CASE 
        WHEN clock_out > clock_in THEN DATEDIFF(HOUR, clock_in, clock_out) 
        ELSE 0 
      END) AS total_work_hours
FROM time_records
GROUP BY employee_id, FORMAT(work_date, 'yyyy-MM');

PostgreSQL #

SELECT
  employee_id,
  TO_CHAR(work_date, 'YYYY-MM') AS pay_month,
  SUM(CASE 
        WHEN clock_out > clock_in THEN EXTRACT(HOUR FROM (clock_out - clock_in)) 
        ELSE 0 
      END) AS total_work_hours
FROM time_records
GROUP BY employee_id, TO_CHAR(work_date, 'YYYY-MM');

If you need more precision (like partial hours), swap HOUR with MINUTE and divide by 60.0 to get decimal hours (e.g., TIMESTAMPDIFF(MINUTE, clock_in, clock_out)/60.0 in MySQL).

Now let's wrap this into a stored procedure that also calculates salary. We'll assume you have an employees table with employee_id, name, and hourly_rate fields.

Example for MySQL/MariaDB #

DELIMITER //

CREATE PROCEDURE CalculateMonthlyPayroll(IN target_month VARCHAR(7))
BEGIN
  -- Join time records with employee data to compute total salary
  SELECT
    e.employee_id,
    e.name,
    target_month AS pay_month,
    COALESCE(SUM(CASE 
                  WHEN tr.clock_out > tr.clock_in THEN TIMESTAMPDIFF(HOUR, tr.clock_in, tr.clock_out) 
                  ELSE 0 
                END), 0) AS total_work_hours,
    e.hourly_rate,
    COALESCE(SUM(CASE 
                  WHEN tr.clock_out > tr.clock_in THEN TIMESTAMPDIFF(HOUR, tr.clock_in, tr.clock_out) 
                  ELSE 0 
                END), 0) * e.hourly_rate AS total_salary
  FROM employees e
  LEFT JOIN time_records tr 
    ON e.employee_id = tr.employee_id
    AND DATE_FORMAT(tr.work_date, '%Y-%m') = target_month
  GROUP BY e.employee_id, e.name, e.hourly_rate;
END //

DELIMITER ;

-- Call the procedure for a specific month (e.g., March 2024)
CALL CalculateMonthlyPayroll('2024-03');

Example for SQL Server #

CREATE PROCEDURE CalculateMonthlyPayroll
  @target_month VARCHAR(7)
AS
BEGIN
  SELECT
    e.employee_id,
    e.name,
    @target_month AS pay_month,
    COALESCE(SUM(CASE 
                  WHEN tr.clock_out > tr.clock_in THEN DATEDIFF(HOUR, tr.clock_in, tr.clock_out) 
                  ELSE 0 
                END), 0) AS total_work_hours,
    e.hourly_rate,
    COALESCE(SUM(CASE 
                  WHEN tr.clock_out > tr.clock_in THEN DATEDIFF(HOUR, tr.clock_in, tr.clock_out) 
                  ELSE 0 
                END), 0) * e.hourly_rate AS total_salary
  FROM employees e
  LEFT JOIN time_records tr 
    ON e.employee_id = tr.employee_id
    AND FORMAT(tr.work_date, 'yyyy-MM') = @target_month
  GROUP BY e.employee_id, e.name, e.hourly_rate;
END;

-- Call the procedure
EXEC CalculateMonthlyPayroll '2024-03';

• Invalid Time Records: The CASE statement ensures we don't count negative hours if clock_out is before clock_in (e.g., a typo).
• No Records for a Month: Using LEFT JOIN and COALESCE ensures employees with no time records still appear with 0 hours and 0 salary.
• Overtime: If you need to calculate overtime (e.g., 1.5x pay for hours over 40/week), add nested CASE statements to split regular and overtime hours.

内容的提问来源于stack exchange，提问作者JoeDoe