Calendar Report Card

This report creates a calendar-style view of employee pay history, displaying pay designations and hours worked for each day of the month in a pivot table format. It provides a visual representation of an employee's work pattern throughout a specified year.

Parameters

• filekey - Employee file key (example: 1) - modify the IN clause value
• Year - Target year for the report (example: 2022) - modify the WHERE clause

Data Components

The report consists of 1 main data transformation section:

1. Daily Pay Pivot - Transforms daily pay records into a calendar grid format

Output Format

The report returns calendar data with the following structure:

Each day column contains pay designation abbreviation and hours in format: "REG - 8.00"

Technical Implementation

The script uses:

• PIVOT operation to transform rows into columns
• DAY() function to extract day of month
• DATENAME() function for month names
• String concatenation for pay display format
• CAST operations for decimal formatting
• INNER JOIN with pay designations for abbreviations

Pivot Structure

The script pivots daily pay records across 31 day columns, handling months with fewer days by showing NULL values for non-existent dates.

Notes

• Currently configured for filekey = 1 and year = 2022
• Displays pay designation abbreviation with hours worked
• Shows all 31 possible days (NULL for non-existent dates)
• Results ordered by employee and month number
• Useful for visualizing work patterns and attendance
• Can be modified to include multiple employees
• Pay information includes all pay designation types
• Format suitable for calendar-style reporting displays

T-SQL

-- Pivot the data using month numbers and add a column for month names
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
        pd.filekey,
        DAY(pd.eventdate) AS DayOfMonth,
        pds.abb + ' - ' + CAST(CAST(pd.Hours AS DECIMAL(10, 2)) AS VARCHAR) AS Hours,
        MONTH(pd.eventdate) AS MonthNumber,
        DATENAME(MONTH, pd.eventdate) AS MonthName,
        pd.PAYDESNUM
    FROM paydeshist pd
    INNER JOIN PAYDESIGNATIONS pds ON pd.PAYDESNUM = pds.PAYDESNUM
    WHERE pd.filekey IN (1) -- Change the filekey value to 1
    AND YEAR(pd.eventdate) = 2022 -- Replace with the desired year
) AS SourceData
PIVOT (
    MAX(Hours) -- 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/pay_information/calendar_report_card.mdx
• SQL Script: SQL/reports/pay_information/calendar_report_card.sql