Weekly Schedule Grouped by Shift XML Report

This report organizes weekly schedules by grouping employees with identical shift patterns and outputs the results in XML format. It provides a structured, machine-readable format for schedule data that can be easily consumed by other systems.

Parameters

No explicit parameters are defined in the script. The report processes schedule data for XML output generation.

Data Components

The report consists of 1 main data section:

XML-Formatted Shift Groups - Groups employees by shift patterns and structures the output as XML

Output Format

The output is structured XML containing:

<ShiftGroups>
    <ShiftGroup>
        <ShiftPattern>07:00-15:00</ShiftPattern>
        <Employees>
            <Employee>John Doe</Employee>
            <Employee>Jane Smith</Employee>
        </Employees>
        <EmployeeCount>2</EmployeeCount>
    </ShiftGroup>
</ShiftGroups>

Technical Implementation

The script uses:

FOR XML clauses to generate XML output
GROUP BY operations on shift time patterns
XML PATH or EXPLICIT modes for structured XML generation
Time formatting for readable shift patterns
Nested XML elements for hierarchical data representation

Use Cases

Data integration with external systems
Automated schedule processing
API responses for schedule data
Data archiving in structured format
Integration with reporting tools that consume XML
Creating schedule feeds for other applications

Benefits

Machine-readable format
Structured data hierarchy
Easy parsing by consuming applications
Standardized data exchange format
Supports complex nested data structures
Compatible with web services and APIs

T-SQL

weekly_schedule_group_by_shift_xml.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,
        STUFF(
            (
                SELECT CHAR(13) + CHAR(10) + EmployeeName
                FROM WeekData AS wd2
                WHERE wd2.Location = wd1.Location
                  AND wd2.Department = wd1.Department
                  AND wd2.ShiftDetails = wd1.ShiftDetails
                  AND wd2.DayOfWeek = wd1.DayOfWeek
                ORDER BY EmployeeName
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
        ) AS Employees
    FROM WeekData AS wd1
    GROUP BY Location, Department, ShiftDetails, DayOfWeek
)
SELECT 
    Location,
    Department,
    ShiftDetails,
    REPLACE(ISNULL([Monday], ''), ',', ',') AS Monday,
    REPLACE(ISNULL([Tuesday], ''), ',', ',') AS Tuesday,
    REPLACE(ISNULL([Wednesday], ''), ',', ',') AS Wednesday,
    REPLACE(ISNULL([Thursday], ''), ',', ',') AS Thursday,
    REPLACE(ISNULL([Friday], ''), ',', ',') AS Friday,
    REPLACE(ISNULL([Saturday], ''), ',', ',') AS Saturday,
    REPLACE(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_xml.mdx
SQL Script: SQL/reports/schedule/weekly_schedule_group_by_shift_xml.sql

Weekly Schedule Grouped by Shift XML Report

On this page