Weekly Schedule Custom Report

This report generates a customized weekly schedule view with specific formatting and filtering options tailored to organizational needs. It provides flexibility in how schedule data is presented and organized.

Parameters

The script likely includes customizable parameters for:

• Date range selection
• Department/work group filtering
• Output format options
• Employee filtering criteria

Data Components

The report consists of customizable data sections:

1. Custom Schedule Layout - Flexible schedule presentation based on requirements
2. Filtered Employee Data - Employee information based on specified criteria
3. Formatted Time Display - Custom time formatting for readability

Output Format

The output format is customizable and may include:

Technical Implementation

The script uses:

• Flexible filtering based on multiple criteria
• Custom formatting functions for time and date display
• Conditional logic for different output scenarios
• Parameterized queries for dynamic filtering
• Custom aggregations based on business requirements

Customization Options

• Date Range Flexibility - Custom start and end dates
• Department Filtering - Specific work groups or departments
• Employee Selection - Active, inactive, or specific employee groups
• Output Formatting - Custom time formats and display options
• Additional Calculations - Business-specific metrics and totals

Use Cases

• Organization-specific schedule layouts
• Custom reporting requirements
• Specialized schedule formats for different departments
• Integration with existing business processes
• Tailored schedule presentations for management
• Custom data exports for external systems

T-SQL

-- Define the date range for the week you want to display
DECLARE @StartDate DATE = {mindate}
DECLARE @EndDate DATE = {maxdate};

WITH WeekData AS (
    SELECT 
        W4.NAME AS Location,
        W2.NAME AS Department,
        E.FILEKEY,
        E.LASTNAME + ', ' + E.FIRSTNAME AS EmployeeName,
        FORMAT(S.SCHDATE, 'dddd') AS DayOfWeek, -- Converts date to day name
        FORMAT(S.STARTTIME, 'hh:mm tt') + '-' + FORMAT(S.ENDTIME, 'hh:mm tt') AS ShiftDetails -- Format time in am/pm
    FROM 
        SCHEDULES S
    INNER JOIN 
        EMPLOYEES E
        ON S.FILEKEY = E.FILEKEY
    INNER JOIN
        WORKGROUP4 W4
        ON S.WG4 = W4.WGNUM
    INNER JOIN
        WORKGROUP2 W2
        ON S.WG2 = W2.WGNUM
    WHERE 
        S.SCHDATE BETWEEN @StartDate AND @EndDate
        AND S.SCHTYPE = 0
)
SELECT 
    Location,
    Department,
    EmployeeName,
    ISNULL([Monday], '') AS Monday,
    ISNULL([Tuesday], '') AS Tuesday,
    ISNULL([Wednesday], '') AS Wednesday,
    ISNULL([Thursday], '') AS Thursday,
    ISNULL([Friday], '') AS Friday,
    ISNULL([Saturday], '') AS Saturday,
    ISNULL([Sunday], '') AS Sunday
FROM 
    WeekData
PIVOT (
    MAX(ShiftDetails) 
    FOR DayOfWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
) AS PivotedSchedule
ORDER BY 1,2,3;

Content Inventory

• Doc File: content/docs/reports/schedule/weekly_schedule_custom.mdx
• SQL Script: SQL/reports/schedule/weekly_schedule_custom.sql