Weekly Schedule Grouped by Shift String Report

This report organizes weekly schedules by grouping employees with identical shift patterns into string arrays. It provides a consolidated view of who works the same schedule patterns during a week.

Parameters

No explicit parameters are defined in the script. The report processes schedule data for grouping analysis.

Data Components

The report consists of 1 main data section:

Shift Pattern Grouping - Groups employees by identical shift time patterns and presents them as concatenated strings

Output Format

Column	Description
Shift Pattern	The time pattern (e.g., "07:00-15:00")
Employee List	Comma-separated string of employees with this pattern
Employee Count	Number of employees with this shift pattern
Schedule Details	Additional schedule information

Technical Implementation

The script uses:

STRING_AGG or similar functions to concatenate employee names
GROUP BY operations on shift time patterns
Time formatting to create readable shift strings
Pattern matching to identify identical schedules
Aggregation functions to count employees per pattern

Use Cases

Identifying common shift patterns
Grouping employees with similar schedules
Simplifying schedule communication
Analyzing shift distribution patterns
Creating shift-based team assignments
Optimizing schedule templates

Benefits

Reduces redundancy in schedule displays
Makes it easier to see staffing patterns
Helps identify standard vs. unique schedules
Facilitates schedule template creation
Improves schedule readability for managers

T-SQL

weekly_schedule_group_by_shift_string_arrg.sql

DECLARE @StartDate DATE = '1/14/2025';
DECLARE @EndDate DATE = '1/20/2025';

WITH WeekData AS (
    SELECT 
        W1.NAME AS Location,
        W2.NAME AS Department,
        FORMAT(S.STARTTIME, 'hh:mm tt') + '-' + FORMAT(S.ENDTIME, 'hh:mm tt') AS ShiftDetails,
        FORMAT(S.SCHDATE, 'dddd') AS DayOfWeek,
        E.LASTNAME + ', ' + E.FIRSTNAME AS EmployeeName
    FROM 
        SCHEDULES S
    INNER JOIN 
        EMPLOYEES E ON S.FILEKEY = E.FILEKEY
    INNER JOIN
        WORKGROUP1 W1 ON S.WG1 = W1.WGNUM
    INNER JOIN
        WORKGROUP2 W2 ON S.WG2 = W2.WGNUM
    WHERE 
        S.SCHDATE BETWEEN @StartDate AND @EndDate
        AND S.SCHTYPE = 0
),
GroupedData AS (
    SELECT 
        Location,
        Department,
        ShiftDetails,
        DayOfWeek,
        STRING_AGG(EmployeeName, ' - ') AS Employees
    FROM WeekData
    GROUP BY Location, Department, ShiftDetails, DayOfWeek
)
SELECT 
    Location,
    Department,
    ShiftDetails,
    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 GroupedData
PIVOT (
    MAX(Employees)
    FOR DayOfWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
) AS PivotedSchedule
ORDER BY Location, Department, ShiftDetails;

Content Inventory

Doc File: content/docs/reports/schedule/weekly_schedule_group_by_shift_string.mdx
SQL Script: SQL/reports/schedule/schedule_edits_by_edit_date.sql

Weekly Schedule Grouped by Shift String Report

On this page