PowerShell to get count of employees working in every hour of the day-CodePudding

I have a CSV of basically a time sheet of IN and Out times. This is a sample.

"Employee Id","Work Date","In","Out"
"1011","01/16/2023","1/16/23 11:04 PM","1/17/23 6:52 AM"
"1012","01/16/2023","1/16/23 11:18 PM","1/17/23 6:05 AM"
"1012","01/17/2023","1/17/23 10:49 PM","1/18/23 7:26 AM"
"1021","01/16/2023","1/16/23 11:18 PM","1/17/23 6:04 AM"
"1021","01/17/2023","1/17/23 10:46 PM","1/18/23 8:12 AM"
"10261","01/16/2023","1/16/23 6:02 AM","1/16/23 12:01 PM"

I've created the intended output file, but I'm stuck on how to get a count of people for each hour. Here is the output creation.

$data = 0..23 | ForEach-Object {
    [PsCustomObject]@{
        WorkDate = "1/17/2023"
        Hour     =  $_
        Count = 0
    }
}
$data

This is the blank Output.

"WorkDate","Hour","Count"
"1/17/2023","0","0"
"1/17/2023","1","0"
"1/17/2023","2","0"
"1/17/2023","3","0"
"1/17/2023","4","0"
"1/17/2023","5","0"
"1/17/2023","6","0"
"1/17/2023","7","0"
"1/17/2023","8","0"
"1/17/2023","9","0"
"1/17/2023","10","0"
"1/17/2023","11","0"
"1/17/2023","12","0"
"1/17/2023","13","0"
"1/17/2023","14","0"
"1/17/2023","15","0"
"1/17/2023","16","0"
"1/17/2023","17","0"
"1/17/2023","18","0"
"1/17/2023","19","0"
"1/17/2023","20","0"
"1/17/2023","21","0"
"1/17/2023","22","0"
"1/17/2023","23","0"

I know the basics of what I need to do, but new to powershell so struggling to put it all together. I need to loop through the output and create a datetime of Workdate Hour (ie 1/17/2023 0:00) and loop through the data to check if that is >= In and <= Out, then iterated a variable and update the count. Then move to the next hour.

This would be the output give my current input example. For example 1012 has 2 entries in the dataset. The first would be counted for hours 0,1,2,3,4,5,6 on 1/17. Same for 1011, and 1012, but 10261 didn't work at all 1/17.

WorkDate,Hour,Count
1/17/2023,0,3
1/17/2023,1,3
1/17/2023,2,3
1/17/2023,3,3
1/17/2023,4,3
1/17/2023,5,3
1/17/2023,6,3
1/17/2023,7,0
1/17/2023,8,0
1/17/2023,9,0
1/17/2023,10,0
1/17/2023,11,0
1/17/2023,12,0
1/17/2023,13,0
1/17/2023,14,0
1/17/2023,15,0
1/17/2023,16,0
1/17/2023,17,0
1/17/2023,18,0
1/17/2023,19,0
1/17/2023,20,0
1/17/2023,21,0
1/17/2023,22,0
1/17/2023,23,0

CodePudding user response：

This will work :

$data = @"
"Employee Id","Work Date","In","Out"
"1011","01/16/2023","1/16/23 11:04 PM","1/17/23 6:52 AM"
"1012","01/16/2023","1/16/23 11:18 PM","1/17/23 6:05 AM"
"1012","01/17/2023","1/17/23 10:49 PM","1/18/23 7:26 AM"
"1021","01/16/2023","1/16/23 11:18 PM","1/17/23 6:04 AM"
"1021","01/17/2023","1/17/23 10:46 PM","1/18/23 8:12 AM"
"10261","01/16/2023","1/16/23 6:02 AM","1/16/23 12:01 PM"
"@

$table = $data | ConvertFrom-Csv

foreach($row in $table)
{
   $row | Add-Member -NotePropertyName InDate -NotePropertyValue ([System.DateTime]::ParseExact($row.In,"M/d/yy h:mm tt",$null))
   $row | Add-Member -NotePropertyName OutDate -NotePropertyValue ([System.DateTime]::ParseExact($row.Out,"M/d/yy h:mm tt",$null))
}


$table | Format-Table
$minDate = $table | Measure-Object -Property InDate -Minimum
Write-Host $minDate.Minimum
$maxDate = $table | Measure-Object -Property OutDate -Maximum
Write-Host $maxDate.Maximum
#subtract minutes to get hour
$minDate = $minDate.minimum.AddMinutes(-$minDate.minimum.Minute)
$maxDate = $maxDate.maximum.AddMinutes(-$maxDate.maximum.Minute)
Write-Host "min date = " $minDate "max date = " $maxDate

$out_table = [System.Collections.ArrayList]::new()
for($date = $minDate; $date -le $maxDate; $date = $date.AddHours(1))
{
   $table | foreach {Write-Host "in = " $_.InDate "out = " $_.OutDate "date = " $date }
   $numEmployees = $table | Where-Object { ($_.InDate -le $date) -and ($_.OutDate -ge $date)}
Write-Host "count = " $numEmployees.Count
   $newRow = New-Object -TypeName psobject
   $newRow | Add-Member -NotePropertyName Hour -NotePropertyValue $date
   if($numEmployees.Count -eq $null) { $count = 0 } else{$count = $numEmployees.Count}
   $newRow | Add-Member -NotePropertyName Count -NotePropertyValue $count
   $out_table.Add($newRow)  | Out-Null
}
$out_table | Format-Table

Results

Hour                  Count
----                  -----
1/16/2023 6:00:00 AM      0
1/16/2023 7:00:00 AM      0
1/16/2023 8:00:00 AM      0
1/16/2023 9:00:00 AM      0
1/16/2023 10:00:00 AM     0
1/16/2023 11:00:00 AM     0
1/16/2023 12:00:00 PM     0
1/16/2023 1:00:00 PM      0
1/16/2023 2:00:00 PM      0
1/16/2023 3:00:00 PM      0
1/16/2023 4:00:00 PM      0
1/16/2023 5:00:00 PM      0
1/16/2023 6:00:00 PM      0
1/16/2023 7:00:00 PM      0
1/16/2023 8:00:00 PM      0
1/16/2023 9:00:00 PM      0
1/16/2023 10:00:00 PM     0
1/16/2023 11:00:00 PM     0
1/17/2023 12:00:00 AM     3
1/17/2023 1:00:00 AM      3
1/17/2023 2:00:00 AM      3
1/17/2023 3:00:00 AM      3
1/17/2023 4:00:00 AM      3
1/17/2023 5:00:00 AM      3
1/17/2023 6:00:00 AM      3
1/17/2023 7:00:00 AM      0
1/17/2023 8:00:00 AM      0
1/17/2023 9:00:00 AM      0
1/17/2023 10:00:00 AM     0
1/17/2023 11:00:00 AM     0
1/17/2023 12:00:00 PM     0
1/17/2023 1:00:00 PM      0
1/17/2023 2:00:00 PM      0
1/17/2023 3:00:00 PM      0
1/17/2023 4:00:00 PM      0
1/17/2023 5:00:00 PM      0
1/17/2023 6:00:00 PM      0
1/17/2023 7:00:00 PM      0
1/17/2023 8:00:00 PM      0
1/17/2023 9:00:00 PM      0
1/17/2023 10:00:00 PM     0
1/17/2023 11:00:00 PM     2
1/18/2023 12:00:00 AM     2
1/18/2023 1:00:00 AM      2
1/18/2023 2:00:00 AM      2
1/18/2023 3:00:00 AM      2
1/18/2023 4:00:00 AM      2
1/18/2023 5:00:00 AM      2
1/18/2023 6:00:00 AM      2
1/18/2023 7:00:00 AM      2
1/18/2023 8:00:00 AM      0

CodePudding user response：

Here's a self-contained example using in-memory CSV in- and output (ConvertFrom-Csv and ConvertTo-Csv); in your real code, use the file-based equivalents (Import-Csv and Export-Csv):

# Initialize an ordered hashtable whose entries will
# map calendar days to arrays with 24 elements each representing
# an hour of the day, with the element values containing the 
# count of clocked-in employees for the given hour.
$hourMap = [ordered] @{}

@'
"Employee Id","Work Date","In","Out"
"1011","01/16/2023","1/16/23 11:04 PM","1/17/23 6:52 AM"
"1012","01/16/2023","1/16/23 11:18 PM","1/17/23 6:05 AM"
"1012","01/17/2023","1/17/23 10:49 PM","1/18/23 7:26 AM"
"1021","01/16/2023","1/16/23 11:18 PM","1/17/23 6:04 AM"
"1021","01/17/2023","1/17/23 10:46 PM","1/18/23 8:12 AM"
"10261","01/16/2023","1/16/23 6:02 AM","1/16/23 12:01 PM"
'@ | 
ConvertFrom-Csv |
ForEach-Object {
  # Get the in an out timestamps as [datetime] instances, 
  # reset to the start of the hour.
  $in, $out =
    ($_.In, $_.Out).ForEach({ Get-Date $_ -Minute 0 -Second 0 -Millisecond 0 })
  # Loop over all hours in the time between in and out.
  $timestamp = $in
  while ($timestamp -le $out) {
    # For the timestamp's calendar day, create a 24-element array 
    # representing the hours of the day; a given element's value will
    # receive the count of clocked-in employees for that hour.
    if (-not $hourMap.Contains($timestamp.Date)) {
      $hourMap[$timestamp.Date] = [int[]]::new(24)
    }
    $hourMap[$timestamp.Date][$timestamp.Hour]  
    $timestamp = $timestamp.AddHours(1)
  }
}

# Create the output objects for a given calendar day
# and convert them to CSV.
$workDate = '1/17/2023'
$hoursToCounts = $hourMap[[datetime] $workDate]
0..23 |
  ForEach-Object {
    [pscustomobject] @{
      WorkDate = $workDate
      Hour     = $_
      Count    = $hoursToCounts[$_]
    }
  } |
  ConvertTo-Csv

Output:

"WorkDate","Hour","Count"
"1/17/2023","0","3"
"1/17/2023","1","3"
"1/17/2023","2","3"
"1/17/2023","3","3"
"1/17/2023","4","3"
"1/17/2023","5","3"
"1/17/2023","6","3"
"1/17/2023","7","0"
"1/17/2023","8","0"
"1/17/2023","9","0"
"1/17/2023","10","0"
"1/17/2023","11","0"
"1/17/2023","12","0"
"1/17/2023","13","0"
"1/17/2023","14","0"
"1/17/2023","15","0"
"1/17/2023","16","0"
"1/17/2023","17","0"
"1/17/2023","18","0"
"1/17/2023","19","0"
"1/17/2023","20","0"
"1/17/2023","21","0"
"1/17/2023","22","2"
"1/17/2023","23","2"