Automated Incident Report

Document Status	DRAFT
Document Owner	@Robert Adach
Business POC	@Wendy Boheim
Related Document Links
Azure Dev Ops Work Item #

Description

The Team requests an automated report to assist in the reporting and tracking of exam incidents. Currently, this is done manually and is a time-consuming but necessary report.

Assumptions

There will be NO changes to the existing legacy applications

Requirements

General Requirements

The report will include ONLY Open or Coded incidents which have a reported date in the current month.
- Example 1: If a incident has a reported date of July 3rd and was coded on July 5th, it will be on a report run in July.
- Example 2: If a incident has a reported date of May 21st and closed July 5th, it will be not be on a report run in July.
- Example 3: If an incident has a reported date of May 31st but was entered into the system on July 1st, it will not be on a report run in July.
The report will include data based on the values at the time the report was run
- If the report date changes it may appear on or be removed from the next generated report
The system will retain ONLY the date and time of the latest change to the Code value.
- If the item was coded on July 1st and then the code was changed on July 7th, the report will show the July 7th date, and this is the date the Days Open column will be calculated on.
Changes to an incident will not have an effect on the Coded date so long as the selected code is not different from the previous value when the incident is saved.

Report data needs

All scripts for database alterations will be located in the [Scripts and Codes] section of this document
Database table “tblIncidents” alterations
- Add a datetime column “CodedDate”, with no default value
- Create an after insert/update trigger that will fire on a change in value to the “Specific Problem” field
  - Trigger will update the CodedDate column with the current date and time.
Create view with the following specifications

Data Values

Column Name	Source data location (tblIncident unless otherwise noted)	Data Type	Notes

Column Name	Source data location (tblIncident unless otherwise noted)	Data Type	Notes
Authorization Number	Actno	String
Date of Exam	ExamDate	Date	Format as MM/DD/YYYY
Date of report	ReportDate	Date	Format as MM/DD/YYYY
Coded Date	CodedDate	Date	Format as MM/DD/YYYY
PV Location #	TestFacility	Number
PV or Candidate report	ReceivedFrom	String
Days Open	CodedDate - ReportDate	Calculated #,	Full numbers only, 0-day calculation
Incident Description	tblRefSpecificProblem.Display	String	Join on tblIncident.SpecificProblem and remove beginning numerical value
Resolution	N/A		Not a database field
Additional Notes	N/A		Not a database field
Multiple Update Requests	N/A		Not a database field

Report contents

Report formatting

I will be adding the new date we are capturing when the ticket was coded as a column between the “Date of Report” and “PV Location #” columns.
- This column will be called “Coded Date”
- The date will be formatted the same as the other date fields
I will be moving the multiple update requests column to the last column as this makes generating the report easier
I will calculate the “Days Open” column by subtracting the “Date of Report” column value from the “Coded Date” value
- This calculation will use a 0-day range, meaning items opened and closed on the same day will be reported as 0 days open

Report delivery

The report will be produced every Monday morning between 2:00 AM and 4:00PM ET
- A specific time will be determined after review of server workloads during this period.
The report will be sent to Jessica and Tessa
- We may add or remove individuals from the report without needing to make any code changes
The report will not automatically be saved to a shared folder.

Design Considerations

Here is a comparison between the current report and the proposed IT report

Known

Test Cases

	Test case description	Result

	Test case description	Result
1	Add a new incident with a report date in the current month and verify it is included on the report	NOT TESTED \| FAILED \| PASSED
2	Add a new incident with a report date of the previous month and verify it is not included on the report
3	Add a closing code to an incident with a report date of the current month and verify it is included in the report
4	Add a closing code to an incident with a report date of the previous month and verify it is not included in the report
5	Add a closing code to an incident, and then change the closing code after a period of time, verify that the CodedDate field in the database has been updated
6	Execute the SQL view manually and compare with the data shown in the report
7	Verify automated execution of the report

Scripts and Codes

Script to add column to tblIncident

MUST ADD ERROR HANDLING

USE [ASWB]
GO

ALTER TABLE tblIncident
ADD CodedDate DATETIME NULL
GO

Script to add trigger


USE [ASWB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--	==========================================================================================
--	Author:		Robert G. Adach
--	Create date: 7/6/2022
--	Description:	This trigger will set the Coded Date field whenever the Code for an 
--	incident is added
--	==========================================================================================
CREATE OR ALTER TRIGGER [dbo].[dev_CodeDateOnUpdate] 
   ON  [dbo].[tblIncident] 
   AFTER INSERT, UPDATE
AS 
BEGIN

	SET NOCOUNT ON;
	IF (UPDATE(SpecificProblem) )
	BEGIN
		UPDATE tblIncident
		SET CodedDate = GETDATE()
		WHERE IncidentID IN (SELECT DISTINCT IncidentID FROM inserted)
	END
END
GO

ALTER TABLE [dbo].[tblIncident] ENABLE TRIGGER [dev_CodeDateOnUpdate]
GO

Script to create view

CREATE OR ALTER VIEW v_IncidentHistory AS (
SELECT
	[Authorization Number] = Actno
	, [Date of Exam] = CAST(ExamDate AS DATE)
	, [Date of Report] = CAST(ReportDate AS DATE)
	, [Date Coded] = CAST(CodedDate AS DATE)
	, [PV Location #] = TestFacility
	, [PV or Candidate report] = ReceivedFrom
	, [Days Open] = CASE WHEN CodedDate IS NOT NULL THEN DATEDIFF(DAY,CAST(ReportDate AS DATE),CAST(CodedDate AS DATE))
			ELSE ''
			END
	, [Incident Description] = SUBSTRING(rsp.Display,CHARINDEX(' ',rsp.Display)+1,LEN(rsp.Display))
	, [Resolution] = ''
	, [Additional Notes] = ''
	, [Multiple Update Requests] = ''
FROM
	tblIncident i
	INNER JOIN tblRefSpecificProblem rsp ON rsp.TheID = i.SpecificProblem
WHERE 
	ReportDate >= DATEADD(DAY,1,EOMONTH(GETDATE(),-1))

)

IT Development Projects