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

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

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
}

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

[quote quote=205251]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

[/quote]

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

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

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

 

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 :slight_smile:

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
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.