Question

46
Views
Julborre Member since 2016 6 posts
Scotiabank
Posted: January 29, 2020
Last activity: January 29, 2020

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:

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

Low-Code App Development Dev/Designer Studio
Share this page LinkedIn