Termination_Call_Detail: identifier of last row of an ACD call

The task:

From Termination_Call_Detail (TCD) we need to select the unique identifier of the final row for each ACD call, for the last 6 months.

The "6 months" clause: uses DateTime (indexed). 

The "ACD call" clause: we use RouterCallKeyDay > 0 AND RouterCallKey > 0 (both are indexed). 

The "unique identifier" part: let's not complicate things and just use RecoveryKey.  

The challenge:

The "final row" - we know there are multiple rows per each ACD call in TCD. 

Let's not complicate things and just pretend rows are ordered by RecoveryKey. 

Analysis:

We've got multiple options: 

• GROUP BY 
• RANK/DENSE_RANK/ROW_NUMBER 
• LAST_VALUE 

 

The good old GROUP BY approach:

SELECT tcd.RouterCallKeyDay, tcd.RouterCallKey, MAX(tcd.RecoveryKey) AS maxTcdRecoveryKey 
FROM Termination_Call_Detail tcd 
WHERE tcd.DateTime > DATEADD(m,-6,CAST(GETDATE() AS DATE)) 
AND tcd.RouterCallKeyDay > 0 AND tcd.RouterCallKey > 0
GROUP BY tcd.RouterCallKeyDay, tcd.RouterCallKey

STATISTICS TIME:  CPU time = 4297 ms,  elapsed time = 17097 ms.

The execution plan is simple. Index seek 85%, Aggregate cost 15%:

 

The fancy ranking approach - using ROW_NUMBER:

;WITH xTcd AS (
SELECT tcd.RouterCallKeyDay, tcd.RouterCallKey, tcd.RecoveryKey, ROW_NUMBER() OVER (PARTITION BY tcd.RouterCallKeyDay, tcd.RouterCallKey ORDER BY tcd.RecoveryKey DESC) AS rowNumber 
FROM Termination_Call_Detail tcd 
WHERE tcd.DateTime > DATEADD(m,-6,CAST(GETDATE() AS DATE)) 
AND tcd.RouterCallKeyDay > 0 AND tcd.RouterCallKey > 0 )
SELECT xTcd.RouterCallKeyDay, xTcd.RouterCallKey, xTcd.RecoveryKey FROM xTcd xTcd WHERE xTcd.rowNumber = 1

Notice we're using a Common Table Expression (CTE) to "store" data temporarily so we can filter out the rows where ROW_NUMBER is 1. 

STATISTICS TIME: CPU time = 6986 ms,  elapsed time = 10153 ms.

Execution plan: yeah, the sort cost is quite high, 45%: 

Finally, the funky analytic approach - using DISTINCT LAST_VALUE:

SELECT DISTINCT tcd.RouterCallKeyDay, tcd.RouterCallKey, LAST_VALUE(tcd.RecoveryKey) OVER (PARTITION BY tcd.RouterCallKeyDay, tcd.RouterCallKey ORDER BY tcd.RecoveryKey DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RecoveryKey 
FROM Termination_Call_Detail tcd 
WHERE tcd.DateTime > DATEADD(m,-6,CAST(GETDATE() AS DATE)) 
AND tcd.RouterCallKeyDay > 0 AND tcd.RouterCallKey > 0

STATISTICS TIME: CPU time = 17375 ms,  elapsed time = 13584 ms.

The execution plan is even more complicated, Index seek: 40%, sort cost: 37%:

 

 

It's not worth using the analytic approach, because... why?!

Now, comparing GROUP BY and ROW_NUMBER: the first approach uses less CPU time (4297ms) but the total elapsed time is 17097ms which is higher than the total elapsed time of the second approach (10153ms) where the CPU time is higher: 6986ms. 

The winner is ROW_NUMBER. 🎉

 

Genesys: Creating TTS prompts via API using HTTP only, no libs

The Uploading Architect Prompts with API document shows how to create prompts using either the JavaScript or the Python library. If you're lazy like me and/or want to avoid using a library, here's how create - TTS - prompts using simple HTTP: 

0. Get the auth token. 

1. Create a blank Prompt object: POST an object as JSON with the following fields: 

- String name,

- (Optional) String description

at https://api.mypurecloud.com/api/v2/architect/prompts

Save the selfUri value from the object returned by the server. 

2. Add a resource to the Prompt created at step 1: POST an object: 

- String language - from the list of supported languages, eg. "en-us",

- String text - the text of the prompt,

- String ttsString - use the same value as for text. 

at https://api.mypurecloud.com${uri}/resources where uri is the value of selfUri from step 1. 

 

Example methods - Groovy + Java 11:

Step 1: 

final def createPrompt(String accessToken, String body) {
 HttpRequest httpRequest = HttpRequest.newBuilder()
  .uri(new URI("https://api.mypurecloud.com/api/v2/architect/prompts"))
  .headers("Content-Type",  "application/json")
  .headers("Authorization", "Bearer $accessToken")
  .timeout(Duration.of(60, ChronoUnit.SECONDS))
  .POST(HttpRequest.BodyPublishers.ofString(body))
  .build()
  HttpResponse<String> httpResponse = HttpClient.newHttpClient().send(httpRequest, HttpResponse.BodyHandlers.ofString()) 
  def statusCode = httpResponse.statusCode()
  println("statusCode:\t$statusCode")
  println("httpResponse:\t${httpResponse.body()}")
 def jsonSlurper = new JsonSlurper()
 def object = jsonSlurper.parseText(httpResponse.body())
 println object
 return object
}

Where accessToken is the access token, and the body is

def o = [name: promptName, description: promptDescription]
def body = groovy.json.JsonOutput.toJson(o)

Step 2: 

final def addTTStoPrompt(String accessToken, String uri, body) {
 HttpRequest httpRequest = HttpRequest.newBuilder()
  .uri(new URI("https://api.mypurecloud.com${uri}/resources"))
  .headers("Content-Type",  "application/json")
  .headers("Authorization", "Bearer $accessToken")
  .timeout(Duration.of(60, ChronoUnit.SECONDS))
  .POST(HttpRequest.BodyPublishers.ofString(body))
  .build()
  HttpResponse<String> httpResponse = HttpClient.newHttpClient().send(httpRequest, HttpResponse.BodyHandlers.ofString()) 
  def statusCode = httpResponse.statusCode()
  println("statusCode:\t$statusCode")
  println("httpResponse:\t${httpResponse.body()}")
 def jsonSlurper = new JsonSlurper()
 def object = jsonSlurper.parseText(httpResponse.body())
 println object
 return object
}

uri is the selfUri value from step 1 and the body:

def o = [language: "en-us", text: ttsString, ttsString: ttsString ]
def body = groovy.json.JsonOutput.toJson(o)