[PowerShell 5.1] [Function] – Issue with Date Comparison

Welcome Forums General PowerShell Q&A [PowerShell 5.1] [Function] – Issue with Date Comparison

  • This topic has 6 replies, 2 voices, and was last updated 3 months ago by
    Participant
    .
Viewing 6 reply threads
  • Author
    Posts
    • #205218
      Participant
      Topics: 1
      Replies: 4
      Points: 22
      Rank: Member

      Hi,

       

      My data is very simple, 4 datetimes.

      Start 1 time
      End 1 time

      Start 2 time
      End 2 time

       

      What i am trying to do is work out what type of overlap it is. I have the query working out if it is an overlap working OK.

       

      Code:

      function OverlapType {
          param ($Time1Start, $Time1End, $Time2Start, $Time2End)
          if ($Time2Start -lt $Time1End -and $Time2Start -gt $Time1Start -and $Time2End -gt $Time1End ) {
              $OverlapType = "Start2 Over End1"
          }
          elseif ($Time2Start -lt $Time1Start -and $Time2End -lt $Time1End -and $Time2Start -gt $Time1Start) {
              $OverlapType = "End2 Over Start1"
          }
          elseif ($Time1Start -lt $Time2End -and $Time2Start -lt $Time1Start -and $Time1End -gt $Time2End ) {
              $OverlapType = "Start1 Over End2"
          }
          elseif ($Time1Start -lt $Time2Start -and ($Time1End -lt $Time2End -and $Time1Start -gt $Time2Start)  ) {
              $OverlapType = "End1 Over Start2"
          }
          elseif ($Time1Start -gt $Time2Start -and $Time1End -lt $Time2End) {
              $OverlapType = "1 In Between 2" 
          }
          elseif ($Time2Start -gt $Time1Start -and $Time2End -lt $Time1End) {
              $OverlapType = "2 In Between 1"
          }
          else {
              $OverlapType = "Unknown From Function"
          }
          return $OverlapType
      }
      

      Can anyone point me in the right direction to fix this issue?

      If you take this time range as an example, it outputs incorrect as 2 between 1 when it should be 1 between 2:
      Time1 start and end: 21/02/2020 09:36:00 – 21/02/2020 10:19:00
      Time2 start and end: 21/02/2020 09:30:00 – 21/02/2020 18:00:00
      Many Thanks
      POC
      • This topic was modified 3 months ago by kvprasoon. Reason: code formatting
    • #205230
      Participant
      Topics: 1
      Replies: 4
      Points: 22
      Rank: Member

      Also tried this and it outputs as Unknown From Function:

      function OverlapType {
          param ([datetime]$Time1Start, [datetime]$Time1End, [datetime]$Time2Start, [datetime]$Time2End)
          $OverlapType = $null
          if ((([datetime]$Time2Start) -lt ([datetime]$Time1End)) -and (([datetime]$Time2Start) -gt ([datetime]$Time1Start)) -and ([datetime]$Time2End )-gt ([datetime]$Time1End) ) {
              $OverlapType = "Start2 Over End1"
          }
          elseif (([datetime]$Time2Start) -lt ([datetime]$Time1Start) -AND (([datetime]$Time2End )-lt ([datetime]$Time1End) -AND ([datetime]$Time2Start) -gt ([datetime]$Time1Start))) {
              $OverlapType = "End2 Over Start1"
          }
          elseif ((([datetime]$Time1Start) -lt ([datetime]$Time2End )-and ([datetime]$Time2Start) -lt ([datetime]$Time1Start)) -AND (([datetime]$Time1End) -gt [datetime]$Time2End) ) {
              $OverlapType = "Start1 Over End2"
          }
          elseif (([datetime]$Time1Start) -lt ([datetime]$Time2Start) -AND (([datetime]$Time1End) -lt ([datetime]$Time2End )-AND ([datetime]$Time1Start) -gt ([datetime]$Time2Start))  ) {
              $OverlapType = "End1 Over Start2"
          }
          elseif (([datetime]$Time1Start) -gt ([datetime]$Time2Start) -AND ([datetime]$Time1End) -lt [datetime]$Time2End) {
              $OverlapType = "1 In Between 2" 
          }
          elseif (([datetime]$Time2Start) -gt ([datetime]$Time1Start) -AND ([datetime]$Time2End )-lt ([datetime]$Time1Start)) {
              $OverlapType = "2 In Between 1"
          }
          else {
              $OverlapType = "Unknown From Function"
          }
          
          return $OverlapType
      }
      
      • This reply was modified 3 months ago by kvprasoon.
    • #205251
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      Maybe you should start a little smaller and extend your code case by case …

      function OverlapType {
          param (
              [datetime]$Time1Start, 
              [datetime]$Time1End, 
              [datetime]$Time2Start, 
              [datetime]$Time2End
          )
      
          if ($Time2Start -ge $Time1Start -and $Time2Start -le $Time1End ) {
              "Start2 between Start1 and End1"
          }
          if ($Time2End -ge $Time1Start -and $Time2End -le $Time1End ) {
              "End2 between Start1 and End1"
          }
          if ($Time1Start -ge $Time2Start -and $Time1Start -le $Time2End ) {
              "Start1 between Start2 and End2"
          }
          if ($Time1End -ge $Time2Start -and $Time1End -le $Time2End ) {
              "End1 between Start2 and End2"
          }
      }
      
      OverlapType -Time1Start 'Jan 1 2020' -Time1End 'Jan 31 2020' -Time2Start 'Jan 15 2020' -Time2End 'Feb 15 2020'

      When you post code please use the code tag button named “PRE”. Thanks

    • #205638
      Participant
      Topics: 1
      Replies: 4
      Points: 22
      Rank: Member

      Maybe you should start a little smaller and extend your code case by case …

      PowerShell
      23 lines

      <textarea class=”ace_text-input” style=”opacity: 0; height: 18px; width: 6.59781px; left: 51px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      function OverlapType {
      param (
      [datetime]$Time1Start,
      [datetime]$Time1End,
      [datetime]$Time2Start,
      [datetime]$Time2End
      )
      if ($Time2Start -ge $Time1Start -and $Time2Start -le $Time1End ) {
      “Start2 between Start1 and End1”
      }
      if ($Time2End -ge $Time1Start -and $Time2End -le $Time1End ) {
      “End2 between Start1 and End1”
      }
      if ($Time1Start -ge $Time2Start -and $Time1Start -le $Time2End ) {
      “Start1 between Start2 and End2”
      }
      if ($Time1End -ge $Time2Start -and $Time1End -le $Time2End ) {
      “End1 between Start2 and End2”
      }
      }
      OverlapType Time1Start ‘Jan 1 2020’ Time1End ‘Jan 31 2020’ Time2Start ‘Jan 15 2020’ Time2End ‘Feb 15 2020’
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      When you post code please use the code tag button named “PRE”. Thanks

      Hi Olaf,

      Thanks for the advice. I did do that but i will try again and come back unless you spotted something?

      Also, i thought i used the code tags and currently appears to me the same as yours so i am a little confused? Unless someone fixed it?

      Many Thanks

      POC

    • #205653
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      Also, i thought i used the code tags and currently appears to me the same as yours so i am a little confused? Unless someone fixed it?

      The mod kvprasoon did it for you. You can see on the little note below your posts. 😉

      • This reply was modified 3 months ago by Olaf.
    • #205674
      Participant
      Topics: 1
      Replies: 4
      Points: 22
      Rank: Member

      So i found the problems, copy paste errors and small complexities from our company 🙂

       

      For completeness, here is my script to look at finding overlapping time entries and classifying them to work out the correct overlapping duration time. Constructive feedback and discussion is of course welcome. If this can be done better, i am all ears 🙂

      Notes:

      1. $SQLPull in my scenario is the same data being compared with each other.
      2. Does not yet understand exactly matching entries. Can be added quite simply, i just dont really have a need for it in my scenario.
      3. $EntriesArray and $EntriesCount are not required. these are there for debugging.
      4. It DOES find the entry conflicting twice each time. This is where entry 1 conflicts with entry 2, and vice versa. Working on this.

      Code:

      #Populate 2 variable objects with the same data
      $Time1 = Invoke-Sqlcmd -Query $SQLPull -ServerInstance "xxxxxxxxx" -Username "xxxxxxx" -Password "xxxxxxxxxxxxx" -Database "xxxxxxxxxxx"
      $Time2 = Invoke-Sqlcmd -Query $SQLPull -ServerInstance "xxxxxxxxxxxx" -Username "xxxxxxxxxxxxx" -Password "xxxxxxxxxxxx" -Database "xxxxxxxx"
        
        
        
      #endregion
        
      #region overlap type function
      function OverlapType {
          param ([datetime]$Time1Start, [datetime]$Time1End, [datetime]$Time2Start, [datetime]$Time2End)
          if ($Time1Start -ge $Time2Start -AND $Time1End -le $Time2End) {
              $OverlapType = "1 In Between 2" 
          }
          elseif ($Time2Start -ge $Time1Start -AND $Time2End -le $Time1End) {
              $OverlapType = "2 In Between 1"
          }
          elseif ((($Time2Start) -lt ($Time1End)) -and (($Time2Start) -gt ($Time1Start)) -and ($Time2End ) -gt ($Time1End) ) {
              $OverlapType = "Start2 Over End1"
          }
          elseif (($Time2Start) -lt ($Time1Start) -AND (($Time2End ) -lt ($Time1End) -AND ($Time2Start) -gt ($Time1Start))) {
              $OverlapType = "End2 Over Start1"
          }
          elseif ((($Time1Start) -lt ($Time2End ) -and ($Time2Start) -lt ($Time1Start)) -AND (($Time1End) -gt $Time2End) ) {
              $OverlapType = "Start1 Over End2"
          }
          elseif (($Time1Start) -lt ($Time2Start) -AND (($Time1End) -lt ($Time2End ) -AND ($Time1Start) -gt ($Time2Start))  ) {
              $OverlapType = "End1 Over Start2"
          }
          else {
              $OverlapType = "Unknown From Function"
          }
          return $OverlapType
      }
      #endregion
      $EntriesArray = @()
      $EntriesCount = $null
      #region process and output
      foreach ($Entry in $Time1) {
          foreach ($SecondEntry in $Time2 ) {
              if ($SecondEntry.Time_Start -lt $Entry.Time_End -and ($null -eq $SecondEntry.Time_End -OR $SecondEntry.Time_End -gt $Entry.Time_Start) -and $Entry.Time_Recid -ne $SecondEntry.Time_Recid -and $Entry.member_id -eq $SecondEntry.member_id) {
                  #Set time entry start and end times and send to function to work out the overlap type
                  ($Time1Start) = $Entry.Time_Start
                  ($Time1End) = $Entry.Time_End
                  ($Time2Start) = $SecondEntry.Time_Start
                  ($Time2End ) = $SecondEntry.Time_End  
                  $OverlapConfirm = OverlapType -Time1Start $Time1Start -Time1End $Time1End -Time2Start $Time2Start -Time2End $Time2End
                  #Find correct duration time based on overlap type
                  switch ($OverlapConfirm) {
                      "Start2 Over End1" { $Duration = New-TimeSpan -Start $SecondEntry.Time_Start -End $Entry.Time_End }
                      "2 In Between 1" { $Duration = New-TimeSpan -Start $SecondEntry.Time_Start -End $SecondEntry.Time_End }
                      "End2 Over Start1" { $Duration = New-TimeSpan -Start $SecondEntry.Time_End -End $Entry.Time_Start }
                      "Start1 Over End2" { $Duration = New-TimeSpan -Start $Entry.Time_Start -End $SecondEntry.Time_End }
                      "1 In Between 2" { $Duration = New-TimeSpan -Start $Entry.Time_Start -End $Entry.Time_End }
                      "End1 Over Start2" { $Duration = New-TimeSpan -Start $Entry.Time_End -End $SecondEntry.Time_Start }
                      "Unknown From Function" { "Function Didnt Pass A Correct Response" }
                      Default { "Unknown From Switch" }
                  }
                  #Entry Found Output
                  Write-Host "Overlap Type: " $OverlapConfirm -ForegroundColor Red
                  Write-Host ([datetime]$Time1Start) " - " ([datetime]$Time1End)   " - " $Entry.Time_RecID
                  Write-Host ([datetime]$Time2Start) " - " ([datetime]$Time2End )  " - " $SecondEntry.Time_RecID
                  Write-Host "Duration "  $Duration.TotalMinutes
                  $EntriesArray += $Entry.Time_RecID 
                  $EntriesCount++
              }
          }
      }
      $EntriesArray
      $EntriesCount
      #endregion

      Added code tages i could see, cant see the “PRE” one? where is that one?

      Many Thanks
      POC
      • This reply was modified 3 months ago by ICEPOC70.
    • #205704
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      Added code tages i could see, cant see the “PRE” one? where is that one?

      It’s right above the editor window. It’s the 3rd button from the right hand side when you select the “Text”-View on the right hand side.

Viewing 6 reply threads
  • The topic ‘[PowerShell 5.1] [Function] – Issue with Date Comparison’ is closed to new replies.