Tyler Technologies - Enterprise CAD Export Script

Extracting the necessary data from your CAD is a key first step in sending your CAD data to Clear/Voyager AI

Corti customers will begin by using an export from their CAD system to identify and share the CAD records of in scope calls. This is often done with a SQL query, but default exports may also work.

For Corti's Clear/Voyager AI solution, Corti looks for the following data from the CAD for each call:

Corti has worked with customers that use Tyler Technologies CAD systems and have used the following script to high degree of success in the past. As all organizations may opt to configure their systems slightly different, this may require modifications to extract the appropriate data points for your organization. The below script can be used in a daily run to extract CAD data for the previous day's activity:

WITH cte AS (
SELECT
	cn.CallID AS NarrativeCallID, 
	ci.IncidentNumber,
	ci.CreateDateTime,
	cn.Narrative,
	cn.EnteredUser,
	gu.UserAlias,
  gu.FirstName,
  gu.LastName,
	cn.MachineID,
	gt.CallTypeID AS IncidentTypeCode,
	gt.Name AS IncidentType,
	ROW_NUMBER() OVER (PARTITION BY cn.CallID ORDER BY ci.CreateDateTime) AS rn
FROM Call.Incident ci
JOIN Call.Call cc ON cc.CallID=ci.CallID
JOIN General.CallType gt ON cc.CallTypeID=gt.CallTypeID
JOIN Call.CallNarrative cn ON ci.CallID=cn.CallID
JOIN General.Users gu ON cn.EnteredUser=gu.UserID
WHERE 
(cn.CreateDatetime >= DATEADD(day, -1, CONVERT(date, GETDATE())) AND cn.CreateDatetime < DATEADD(day, -0, CONVERT(date, GETDATE())))
AND
	(cn.Narrative like '%ANSWERS:%')
)
SELECT NarrativeCallID, 
	IncidentNumber, 
	CreateDateTime,
	Narrative,
	EnteredUser,
	UserAlias,
  FirstName,
  LastName,
	MachineID,
	IncidentTypeCode,
	IncidentType
FROM cte
WHERE rn = 1;