Calendar Report Card - Incidents

Parameters

The script is currently configured with hardcoded values:

• Employee: filekey = 1 - Specific employee to analyze
• Year: 2022 - Year for incident analysis

Note: These values should be modified in the script before execution.

Data Components

The report consists of 2 main sections:

1. Employee and Month Information - Employee identifier and month name
2. Daily Incident Grid - 31-day calendar layout showing incident abbreviations

Output Format

The report displays incident abbreviations in a calendar format where each column represents a day of the month (1-31).

Technical Implementation

The script uses:

• PIVOT operation - To transform daily incident data into calendar columns
• DAY() function - To extract day of month for pivot columns
• DATENAME() function - To get month names for display
• WHERE clause filtering - Excludes rule source 1 incidents and filters by employee/year
• MAX() aggregate - Handles multiple incidents on the same day
• INCIDENTS table - Source for incident data with abbreviations
• Dynamic column generation - Creates columns for all 31 possible days

T-SQL

SELECT
    filekey,
    MonthName,
    [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5,
    [6] AS Day6, [7] AS Day7, [8] AS Day8, [9] AS Day9, [10] AS Day10,
    [11] AS Day11, [12] AS Day12, [13] AS Day13, [14] AS Day14, [15] AS Day15,
    [16] AS Day16, [17] AS Day17, [18] AS Day18, [19] AS Day19, [20] AS Day20,
    [21] AS Day21, [22] AS Day22, [23] AS Day23, [24] AS Day24, [25] AS Day25,
    [26] AS Day26, [27] AS Day27, [28] AS Day28, [29] AS Day29, [30] AS Day30, [31] AS Day31
FROM (
    SELECT
        filekey,
		MONTH(eventdate) AS MonthNumber,
        DATENAME(MONTH, eventdate) AS MonthName,
        DAY(eventdate) AS DayOfMonth,
        INCIDENTABB
    FROM INCIDENTS
    WHERE rulesource NOT IN (1)
        AND filekey = 1
        AND YEAR(eventdate) = 2022
) AS SourceData
PIVOT (
    MAX(INCIDENTABB) -- Use MAX to handle string concatenation
    FOR DayOfMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                     [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
                     [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS PivotTable
ORDER BY filekey, MonthNumber;

Content Inventory

• Doc File: content/docs/reports/employee_information/calendar_report_card_incidents.mdx
• SQL Script: SQL/reports/employee_information/calendar_report_card_incidents.sql