Author:  Marnel Catlett

Info:  As your data is on Prem, we do not have access to establish "where" to start ingesting your Service Audit records from.  We want to get a decent set of data without creating any issues with the performance.  To do that, we need your help.

Could you please send the following information to:  provisioning@cognition360.com

TABLE OF CONTENTS


Step 1: Run the following two queries to establish the starting date and number of records for Service Audit records

  • Each of these will tell us the current record count and the record count for 2020 and 2021.  From there we can establish the volume of records and where (date in time) we should start ingesting data from.
  • A screenshot of the results is good enough.
SELECT MIN (SR_Service_Audit_RecID) AS [Earliest Record #],
MAX (SR_Service_Audit_RecID) AS [Oldest Record ],
MIN (Date_Entered_UTC) AS [EarliestDate],
MAX (Date_Entered_UTC) AS [LatestDate]
FROM SR_Service_Audit
WHERE Date_Entered_UTC > '2020-01-01'
 
SELECT MIN (SR_Service_Audit_RecID) AS [Earliest Record #],
MAX (SR_Service_Audit_RecID) AS [Oldest Record ],
MIN (Date_Entered_UTC) AS [EarliestDate],
MAX (Date_Entered_UTC) AS [LatestDate]
FROM SR_Service_Audit
WHERE Date_Entered_UTC > '2021-01-01'

Step 2: Run the second script.  This will tell us if there are any large data gaps (from where records were deleted).  We typically work on an up to 5000 records gap, but any more than that could delay the process.


  • This will tell us if there are any large data gaps (from where records were deleted).  We typically work on an up to 5000 records gap, but any more than that could delay the process.


--The below query will tell us if there are large numbers of records that has been deleted which will affect the ingestion process.  We are only needing to know if any of those "RownumberDiscrepancies" are greater than 5000.
WITH RowIssues AS 
(
SELECT 
CASE WHEN SR_Service_Audit_RecID - LAG(SR_Service_Audit_RecID, 1, 0) OVER (ORDER BY Sr_Service_Audit_RecID) > 5 THEN 'Issues' ELSE 'Good' END AS Review,
LAG(SR_Service_Audit_RecID, 1, 0) OVER (ORDER BY Sr_Service_Audit_RecID) SR_Service_Audit_RecID_Lag, SR_Service_Audit_RecID
FROM
       SR_Service_Audit
WHERE
       SR_Service_Audit_RecID >= 0                 -------->  This number can be adjusted to the REC_ID listed under the earliest date from the query above. (Earliest Record #) from the first query.
)
 
SELECT b.*, b.SR_Service_Audit_RecID - b.SR_Service_Audit_RecID_Lag  [RowNumberDiscrepancy], a.* 
FROM 
       SR_Service_Audit a
       INNER JOIN RowIssues b ON a.SR_Service_Audit_RecID = b.SR_Service_Audit_RecID
WHERE b.Review = 'Issues'
--ORDER BY SR_Service_Audit_RecID_Lag
ORDER BY RowNumberDiscrepancy DESC
If there are numbers larger than 5000, it would be helpful to have a screenshot of those record ID's so that we can adjust the ingestion process to get all the data.  


Step 3: Please send us the information via email 


For queries, please contact support@cognition360.com