SQL Server计算员工月度工作时长并写入PeriodWorks表求助

Hey there! Let's work through this problem together. Since I don't have access to your table screenshots, I'll start with reasonable assumptions about the schema of your PointingSystem and PeriodWorks tables—these align with typical employee time-tracking setups:

Assumed Table Schemas #

• PointingSystem: Stores daily punch-in/out records with fields like:
  • EmployeeID (unique identifier for each employee)
  • Input (datetime of punch-in)
  • Output (datetime of punch-out)
• PeriodWorks: Stores monthly aggregated work hours with fields like:
  • EmployeeID (matches the one in PointingSystem)
  • ReportMonth (month identifier, e.g., first day of the month as DATE or formatted string like '2024-05')
  • TotalWorkHours (total calculated work hours for the month)

Solution 1: Full Refresh (Overwrite All Monthly Data) #

If you want to completely refresh the PeriodWorks table with the latest data from PointingSystem, use this script. Note: This will clear existing data in PeriodWorks first—only use this if you don't need to preserve old records!

-- Clear existing data in PeriodWorks (optional, use with caution)
TRUNCATE TABLE PeriodWorks;

-- Insert aggregated monthly work hours
INSERT INTO PeriodWorks (EmployeeID, ReportMonth, TotalWorkHours)
SELECT
    EmployeeID,
    -- Generate the first day of the month as the report month identifier
    DATEFROMPARTS(YEAR(Input), MONTH(Input), 1) AS ReportMonth,
    -- Calculate total work hours (convert seconds to hours with 2 decimal places)
    SUM(DATEDIFF(SECOND, Input, Output)) / 3600.0 AS TotalWorkHours
FROM PointingSystem
-- Filter out invalid records where punch-out time is earlier than punch-in
WHERE Input < Output
GROUP BY EmployeeID, YEAR(Input), MONTH(Input)
ORDER BY EmployeeID, ReportMonth;

Solution 2: Incremental Update (Add New Monthly Data Only) #

If you want to add only new monthly records that haven't been inserted into PeriodWorks yet (to avoid overwriting existing data), use this script:

-- Insert only unprocessed monthly data into PeriodWorks
INSERT INTO PeriodWorks (EmployeeID, ReportMonth, TotalWorkHours)
SELECT
    ps.EmployeeID,
    DATEFROMPARTS(YEAR(ps.Input), MONTH(ps.Input), 1) AS ReportMonth,
    SUM(DATEDIFF(SECOND, ps.Input, ps.Output)) / 3600.0 AS TotalWorkHours
FROM PointingSystem ps
LEFT JOIN PeriodWorks pw
    ON ps.EmployeeID = pw.EmployeeID
    AND DATEFROMPARTS(YEAR(ps.Input), MONTH(ps.Input), 1) = pw.ReportMonth
-- Only select months that aren't already in PeriodWorks
WHERE pw.EmployeeID IS NULL
  AND ps.Input < ps.Output -- Exclude invalid punch records
GROUP BY ps.EmployeeID, YEAR(ps.Input), MONTH(ps.Input)
ORDER BY ps.EmployeeID, ReportMonth;

Key Notes & Adjustments #

• If your ReportMonth uses a string format: Replace DATEFROMPARTS(YEAR(Input), MONTH(Input), 1) with FORMAT(ps.Input, 'yyyy-MM') to get a string like '2024-05'.
• Handling time precision: If you prefer minutes instead of hours, use SUM(DATEDIFF(MINUTE, Input, Output)) / 60.0 instead of the second-based calculation.
• Performance optimization: For large datasets, add an index to PointingSystem to speed up grouping:

  CREATE NONCLUSTERED INDEX IX_PointingSystem_EmployeeID_Input 
  ON PointingSystem(EmployeeID, Input);
  

• Edge cases: The WHERE Input < Output clause filters out invalid punch records (e.g., accidental punch-out before punch-in). You can remove this if you want to include negative durations (though that's usually not desirable).

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