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

File Type

Key Data Points

.csv

Incident ID - or other unique Identifier

Call Start Time

Workstation ID - Identifier to pair to channel from Call Recorder

Call Taker ID

Protocol ID - Chief complaint, etc. - this is how the incident protocol will be displayed within Corti

Corti has worked with customers that use CentralSquare 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
	rc.Master_Incident_ID,
	rmi.Master_Incident_Number,
	rc.Performed_By,
	p.Emp_Name,
	rmi.Response_Date,
	rmi.MachineName,
	rmi.Problem,
	ROW_NUMBER() OVER (PARTITION BY rc.Master_Incident_ID ORDER BY rmi.Response_Date) AS rn
FROM Response_Comments rc
JOIN Response_Master_Incident rmi ON rc.Master_Incident_ID=rmi.ID
JOIN Personnel p ON rc.Performed_By=p.Emp_ID
WHERE 
		(rmi.Response_Date >= DATEADD(day, -1, CONVERT(date, GETDATE())) AND rmi.Response_Date < DATEADD(day, -0, CONVERT(date, GETDATE()))) 
AND
	rmi.Agency_Type in ('Police')
)
SELECT  
	Master_Incident_Number,
	Performed_By,
	Emp_Name,
	Response_Date,
	MachineName,
	Problem
FROM cte
WHERE rn = 1 and MachineName is not NULL