Calendar Report Card (Month as Columns

Comprehensive calendar-style view that combines both pay designation hours and exceptions for active employees within a specified date range, displaying months as columns and days as rows

Parameters

{mindate} - Uses Date filter begin date
{maxdate} - Uses Date filter end date

Download Alternative Formats

📥 AOD Browser Report Format:

Download AOD Import File - Import this report directly into AOD Service Module

Data Components

The report consists of three main parts:

NumberedData CTE - Retrieves pay designation history for specific pay types
ExceptionData CTE - Retrieves exception history for specific exception types
Combined Query - Combines and pivots both sets of data to display months as columns and days as rows

Example Output

Calendar Report Card Combined Example

💡 Click the image above to zoom in and see the calendar details more clearly!

The report outputs a calendar-style table with employee names and days of the month as rows and months as columns. Each cell contains either the pay designation abbreviation and hours worked or exception abbreviation and hours in the format: "ABB - HH.HH" (where ABB is the pay designation or exception abbreviation and HH.HH is the hours).

Technical Implementation

The script uses:

Multiple Common Table Expressions (CTEs) to prepare and format different data types
DAY(), MONTH(), and DATENAME() functions to extract date components
Conditional aggregation with CASE statements to pivot months into columns
UNION operation to combine pay designation and exception data
String concatenation to format output cells
DECIMAL(10,2) casting to ensure consistent hour formatting
Conversion of exception minutes to hours (AMOUNT/60)
Filtering for active employees only (ACTIVESTATUS = 0)
Filtering for specific pay designation numbers and exception types

T-SQL

calendar_report_card_month_as_columns_combined.sql

DECLARE @MIN DATE, @MAX DATE

/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='9/1/2021'
SET @MAX='9/30/2021'
;

WITH NumberedData AS (
    -- Query 1
    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 (Select filekey from EMPLOYEES where ACTIVESTATUS = 0)
    AND pd.eventdate between @MIN and @MAX
    AND pd.PAYDESNUM IN (1,2,3,4,6,7,10,12,14,15,23,25,26,27,29,32,33,38,39)
),
ExceptionData AS (
    -- Query 2
    SELECT
        ex.filekey,
        DAY(ex.eventdate) AS DayOfMonth,
        e.ABB + ' - ' + CAST(CAST(ex.AMOUNT/60 AS DECIMAL(10, 2)) AS VARCHAR) AS ExAmount,
        MONTH(ex.eventdate) AS MonthNumber,
        DATENAME(MONTH, ex.eventdate) AS MonthName,
		ex.EXCEPTION
    FROM EXCEPTIONHIST ex
    INNER JOIN EXCEPTIONS e ON ex.EXCEPTION = e.NUM
    WHERE ex.filekey IN (Select filekey from EMPLOYEES where ACTIVESTATUS = 0)
    AND ex.eventdate between @MIN and @MAX
    AND ex.EXCEPTION IN (2,3,10)
)

-- Combine the results
SELECT
    e.LASTNAME+', '+e.FIRSTNAME as Emp,
    DayOfMonth,
    MAX(CASE WHEN MonthNumber = 1 THEN Hours END) AS January,
    MAX(CASE WHEN MonthNumber = 2 THEN Hours END) AS February,
    MAX(CASE WHEN MonthNumber = 3 THEN Hours END) AS March,
    MAX(CASE WHEN MonthNumber = 4 THEN Hours END) AS April,
    MAX(CASE WHEN MonthNumber = 5 THEN Hours END) AS May,
    MAX(CASE WHEN MonthNumber = 6 THEN Hours END) AS June,
    MAX(CASE WHEN MonthNumber = 7 THEN Hours END) AS July,
    MAX(CASE WHEN MonthNumber = 8 THEN Hours END) AS August,
    MAX(CASE WHEN MonthNumber = 9 THEN Hours END) AS September,
    MAX(CASE WHEN MonthNumber = 10 THEN Hours END) AS October,
    MAX(CASE WHEN MonthNumber = 11 THEN Hours END) AS November,
    MAX(CASE WHEN MonthNumber = 12 THEN Hours END) AS December,
	nd.PAYDESNUM
FROM NumberedData nd
Inner Join EMPLOYEES e
on nd.FILEKEY = e.FILEKEY
GROUP BY nd.FILEKEY,e.LASTNAME,e.FIRSTNAME, DayOfMonth, PAYDESNUM

UNION

SELECT
    e.LASTNAME+', '+e.FIRSTNAME as Emp,
    DayOfMonth,
    MAX(CASE WHEN MonthNumber = 1 THEN ExAmount END) AS January,
    MAX(CASE WHEN MonthNumber = 2 THEN ExAmount END) AS February,
    MAX(CASE WHEN MonthNumber = 3 THEN ExAmount END) AS March,
    MAX(CASE WHEN MonthNumber = 4 THEN ExAmount END) AS April,
    MAX(CASE WHEN MonthNumber = 5 THEN ExAmount END) AS May,
    MAX(CASE WHEN MonthNumber = 6 THEN ExAmount END) AS June,
    MAX(CASE WHEN MonthNumber = 7 THEN ExAmount END) AS July,
    MAX(CASE WHEN MonthNumber = 8 THEN ExAmount END) AS August,
    MAX(CASE WHEN MonthNumber = 9 THEN ExAmount END) AS September,
    MAX(CASE WHEN MonthNumber = 10 THEN ExAmount END) AS October,
    MAX(CASE WHEN MonthNumber = 11 THEN ExAmount END) AS November,
    MAX(CASE WHEN MonthNumber = 12 THEN ExAmount END) AS December,
	999
FROM ExceptionData ed
Inner Join EMPLOYEES e
on ed.FILEKEY = e.FILEKEY
GROUP BY ed.FILEKEY,e.LASTNAME,e.FIRSTNAME, DayOfMonth, EXCEPTION
ORDER BY Emp, DayOfMonth, nd.PAYDESNUM;

Content Inventory

Doc File: content/docs/reports/employee_information/calendar_report_card_month_as_columns_combined.mdx
SQL Script: SQL/reports/employee_information/calendar_report_card_month_as_columns_combined.sql
Screenshot: public/img/screenshots/reports/employee_information/calendar_report_card_month_as_columns_combined.png
AOD Browser Report: public/aod/browser_reports/employee_information/Calendar_Report_Card_Combined.browser_report

Calendar Report Card (Month as Columns - Combined)