SLA is [SLA-Timeliness] Function has a Bug?
For the following data:
<table border='1'>
<tr>
<th>Resolved date time</th>
<th>Deadline date time</th>
</tr>
<tr>
<td>1/27/2020 6:54 AM</td>
<td>1/27/2020 2:45 PM</td>
</tr>
</table>
This function returns 1!
As you could see the resolved date was 27th Jan, deadline 28th, the logic used by this function when the 1st parameter is past deadline is as follows:
For the following data:
<table border='1'>
<tr>
<th>Resolved date time</th>
<th>Deadline date time</th>
</tr>
<tr>
<td>1/27/2020 6:54 AM</td>
<td>1/27/2020 2:45 PM</td>
</tr>
</table>
This function returns 1!
As you could see the resolved date was 27th Jan, deadline 28th, the logic used by this function when the 1st parameter is past deadline is as follows:
<p:when test=".pyParameters(1).pyParametersParamValue == '\"past deadline\"'">
<p:when test=".pyParameters(4).pyDBDataType =='TIMESTAMP' && .pyParameters(3).pyDBDataType =='TIMESTAMP'">
CASE WHEN CAST(EXTRACT('day' from({4} - {3})) AS INT) >= 0 THEN 1 ELSE 0 END
</p:when>
<p:when test=".pyParameters(4).pyDBDataType =='TIMESTAMP' && .pyParameters(3).pyDBDataType =='VARCHAR'">
CASE WHEN CAST(EXTRACT('day' from ({4} - (to_timestamp({3}, 'YYYYMMDD 00:00:00')::timestamp without time zone))) AS INT) >= 0 THEN 1 ELSE 0 END
</p:when>
<p:when test=".pyParameters(4).pyDBDataType =='VARCHAR' && .pyParameters(3).pyDBDataType =='TIMESTAMP'">
CASE WHEN CAST(EXTRACT('day' from ((to_timestamp({4}, 'YYYYMMDD 00:00:00')::timestamp without time zone)- {3})) AS INT) >= 0 THEN 1 ELSE 0 END
</p:when>
<p:when test=".pyParameters(4).pyDBDataType =='VARCHAR' && .pyParameters(3).pyDBDataType =='VARCHAR'">
CASE WHEN CAST(EXTRACT('day' from ((to_timestamp({4}, 'YYYYMMDD 00:00:00')::timestamp without time zone) - (to_timestamp({3}, 'YYYYMMDD 00:00:00')::timestamp without time zone))) AS INT) >= 0 THEN 1 ELSE 0 END
</p:when>
</p:when>
I think this logic is flawed because the function also needs to check the hours and minutes to decide when the resolving date was within the deadline date.
When I have the following data:
<table border='1'>
<tr>
<th>Resolved date time</th>
<th>Deadline date time</th>
</tr>
<tr>
<td>1/27/2020 10:26 AM</td>
<td>1/28/2020 9:25 AM</td>
</tr>
</table>
The function returns 0! because: 27-28 = -1 ...