使用PowerShell统计指定日期各小时在岗员工数量

Got it, let's put this all together step by step. Here's how you can integrate your blank hourly structure with the CSV attendance data to count how many employees were on-site during each hour of 1/17/2023:

Step 1: Import and Prepare Attendance Data #

First, we need to read your CSV file and convert the In/Out strings to proper DateTime objects—this makes time comparisons much easier.

Step 2: Process Hourly Count Logic #

We'll take your pre-generated hourly structure, then for each hour, check every employee's attendance record to see if they were on-site during that hour. We'll increment the Count whenever there's an overlap between the hour window and the employee's shift.

Full PowerShell Code #

# 1. Import your CSV attendance data (replace the path with your actual file path)
$attendanceData = Import-Csv -Path "C:\path\to\your\attendance.csv"

# 2. Generate the blank hourly count structure for the target date
$targetDate = "1/17/2023"
$data = 0..23 | ForEach-Object {
    [PsCustomObject]@{
        WorkDate = $targetDate
        Hour     = $_
        Count    = 0
    }
}

# 3. Iterate over each hourly entry to calculate counts
foreach ($hourEntry in $data) {
    # Create the start and end datetime for the current hour window
    $hourStart = [DateTime]"$($hourEntry.WorkDate) $($hourEntry.Hour):00:00"
    $hourEnd = $hourStart.AddHours(1)

    # Check each employee's attendance record
    foreach ($record in $attendanceData) {
        # Convert In/Out times to DateTime objects
        $inTime = [DateTime]$record.In
        $outTime = [DateTime]$record.Out

        # Check if the employee's shift overlaps with the current hour window
        # Logic: If the shift starts before the hour ends AND ends after the hour starts, there's an overlap
        if ($inTime -lt $hourEnd -and $outTime -gt $hourStart) {
            $hourEntry.Count++
        }
    }
}

# 4. Output the final counted data
$data

Key Logic Explanation #

• Hour Window Creation: For each hour (e.g., 0 for midnight), we create a DateTime range from 1/17/2023 00:00:00 to 1/17/2023 01:00:00.
• Overlap Check: The condition $inTime -lt $hourEnd -and $outTime -gt $hourStart handles all shift scenarios, including:
  • Shifts that start before the target date and end during it (e.g., employee 1011's shift from 1/16 11PM to 1/17 6:52AM)
  • Shifts that start during the target date and end after it (e.g., employee 1012's shift from 1/17 10:49PM to 1/18 7:26AM)
  • Full shifts within the target date (though your sample doesn't have this, but the logic covers it)

Sample Output for Your Data #

Running this code with your sample CSV will produce results like this (abbreviated):

WorkDate  Hour Count
--------- ---- -----
1/17/2023    0     3  # Employees 1011, 1012, 1021 are on-site
1/17/2023    1     3
...
1/17/2023    6     2  # 1011 and 1021 are still on-site; 1012 left at 6:05AM
1/17/2023    7     1  # Only 1021 is on-site until 8:12AM
...
1/17/2023   22     2  # Employees 1012 and 1021 start their night shifts
1/17/2023   23     2

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