如何重构SQL Server中员工上下班打卡时间的输出格式？

Hey there! Since you're new to SQL but already building views, let's tackle this monthly payroll report task step by step. Your current output shows multiple clock-in/out entries per day for each employee—first we need to aggregate these into daily total hours, then roll that up into monthly payroll metrics.

Step 1: Calculate Daily Total Work Hours #

First, let's create a CTE (Common Table Expression) to sum up all the time each employee worked per day. We'll use DATEDIFF to calculate the minutes between each clock-in and clock-out, then convert that to hours for readability:

WITH DailyWorkHours AS (
    SELECT
        Date,
        Name,
        -- Calculate total minutes worked, convert to decimal hours
        SUM(DATEDIFF(MINUTE, [Clock In], [Clock Out])) / 60.0 AS TotalDailyHours
    FROM YourClockInTable  -- Replace with your actual table/view name
    WHERE [Clock Out] IS NOT NULL  -- Exclude entries with missing clock-out times
    GROUP BY Date, Name
)

This groups all entries by date and employee, giving us a single row per person per day with their total hours worked.

Step 2: Roll Up to Monthly Payroll Metrics #

Next, we'll take the daily hours and calculate monthly totals, including regular hours, overtime (assuming standard 8-hour workdays), and total pay. Adjust the pay rules to match your company's policy:

SELECT
    Name,
    DATEPART(YEAR, Date) AS PayrollYear,
    DATEPART(MONTH, Date) AS PayrollMonth,
    -- Sum regular hours (capped at 8 hours/day)
    SUM(CASE WHEN TotalDailyHours <= 8 THEN TotalDailyHours ELSE 8 END) AS RegularHours,
    -- Sum overtime hours (any time over 8 hours/day)
    SUM(CASE WHEN TotalDailyHours > 8 THEN TotalDailyHours - 8 ELSE 0 END) AS OvertimeHours,
    -- Calculate total pay (replace hourly rate with your actual value or join to a pay rate table)
    (SUM(CASE WHEN TotalDailyHours <= 8 THEN TotalDailyHours ELSE 8 END) * 25.00) +
    (SUM(CASE WHEN TotalDailyHours > 8 THEN TotalDailyHours - 8 ELSE 0 END) * 25.00 * 1.5) AS TotalMonthlyPay
FROM DailyWorkHours
GROUP BY Name, DATEPART(YEAR, Date), DATEPART(MONTH, Date)
ORDER BY PayrollYear, PayrollMonth, Name;

Key Notes: #

• Overtime Rules: If your company uses different overtime thresholds (e.g., 40 hours/week instead of 8 hours/day), you'll need to adjust the logic to group by week first, then roll up to months.
• Pay Rates: Instead of hardcoding the hourly rate (25.00), you can join this query to an employee pay rate table to pull in individual rates dynamically.
• Data Quality: Add checks for invalid entries (e.g., clock-out times earlier than clock-in times) using a WHERE clause like [Clock Out] > [Clock In].

Optional: Save as a View #

Since you're already working with views, you can wrap this entire logic into a view for easy reuse:

CREATE VIEW MonthlyPayrollReport AS
WITH DailyWorkHours AS (
    SELECT
        Date,
        Name,
        SUM(DATEDIFF(MINUTE, [Clock In], [Clock Out])) / 60.0 AS TotalDailyHours
    FROM YourClockInTable
    WHERE [Clock Out] IS NOT NULL AND [Clock Out] > [Clock In]
    GROUP BY Date, Name
)
SELECT
    Name,
    DATEPART(YEAR, Date) AS PayrollYear,
    DATEPART(MONTH, Date) AS PayrollMonth,
    SUM(CASE WHEN TotalDailyHours <= 8 THEN TotalDailyHours ELSE 8 END) AS RegularHours,
    SUM(CASE WHEN TotalDailyHours > 8 THEN TotalDailyHours - 8 ELSE 0 END) AS OvertimeHours,
    (SUM(CASE WHEN TotalDailyHours <= 8 THEN TotalDailyHours ELSE 8 END) * 25.00) +
    (SUM(CASE WHEN TotalDailyHours > 8 THEN TotalDailyHours - 8 ELSE 0 END) * 25.00 * 1.5) AS TotalMonthlyPay
FROM DailyWorkHours
GROUP BY Name, DATEPART(YEAR, Date), DATEPART(MONTH, Date);

Now you can query the view anytime to get up-to-date payroll reports:

SELECT * FROM MonthlyPayrollReport ORDER BY PayrollYear, PayrollMonth, Name;

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