如何为SQL工时统计结果添加日期范围对应的月份标记？

Got it, let's tackle this problem head-on. The core goal is to dynamically adjust our output based on whether the user picks a single-month or cross-month date range—either tagging all records with the same month label, or splitting records across overlapping months with their respective labels. Here's a step-by-step solution:

Core Approach #

1. Detect range type: Check if the start and end dates fall within the same month. If yes, we can apply a static month label to all results.
2. Generate month list for cross-range: Use a recursive CTE to create a list of every month (full or partial) covered by the input date range.
3. Split & calculate monthly hours: For work sessions that span multiple months, split the record into separate rows for each overlapping month, and calculate only the hours that belong to that specific month.

Complete Stored Procedure Code #

CREATE PROCEDURE sp_GetEmployeeHours
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Recursive CTE to generate all relevant months in the date range
    WITH MonthList AS (
        SELECT
            DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1) AS MonthStart,
            -- Ensure first month's end doesn't exceed the input end date
            CASE 
                WHEN EOMONTH(@StartDate) > @EndDate THEN @EndDate 
                ELSE EOMONTH(@StartDate) 
            END AS MonthEnd
        UNION ALL
        SELECT
            DATEADD(MONTH, 1, ml.MonthStart),
            CASE 
                WHEN EOMONTH(DATEADD(MONTH, 1, ml.MonthStart)) > @EndDate THEN @EndDate 
                ELSE EOMONTH(DATEADD(MONTH, 1, ml.MonthStart)) 
            END AS MonthEnd
        FROM MonthList ml
        WHERE DATEADD(MONTH, 1, ml.MonthStart) <= @EndDate
    ),
    -- Base employee hours data (replace with your actual table/columns)
    EmployeeHours AS (
        SELECT
            EmployeeID,
            EmployeeName,
            WorkStartTime, -- DATETIME of work start
            WorkEndTime,   -- DATETIME of work end
            -- Calculate total hours for the original session
            DATEDIFF(MINUTE, WorkStartTime, WorkEndTime) / 60.0 AS TotalHours
        FROM YourEmployeeHoursTable
        WHERE WorkStartTime BETWEEN @StartDate AND DATEADD(DAY, 1, @EndDate) -- Include end date's full day
    )

    -- Final output: handle both single and cross-month scenarios
    SELECT
        eh.EmployeeID,
        eh.EmployeeName,
        -- Generate human-readable month tag (e.g., Jan 2019)
        FORMAT(ml.MonthStart, 'MMM yyyy') AS MonthTag,
        -- Calculate hours that fall within the current month
        CASE
            -- Session is entirely within the month
            WHEN eh.WorkStartTime >= ml.MonthStart AND eh.WorkEndTime <= ml.MonthEnd THEN eh.TotalHours
            -- Session starts before the month, ends within it
            WHEN eh.WorkStartTime < ml.MonthStart AND eh.WorkEndTime <= ml.MonthEnd THEN DATEDIFF(MINUTE, ml.MonthStart, eh.WorkEndTime) / 60.0
            -- Session starts within the month, ends after it
            WHEN eh.WorkStartTime >= ml.MonthStart AND eh.WorkEndTime > ml.MonthEnd THEN DATEDIFF(MINUTE, eh.WorkStartTime, ml.MonthEnd) / 60.0
            -- Session spans the entire month
            WHEN eh.WorkStartTime < ml.MonthStart AND eh.WorkEndTime > ml.MonthEnd THEN DATEDIFF(MINUTE, ml.MonthStart, ml.MonthEnd) / 60.0
        END AS HoursInMonth
    FROM EmployeeHours eh
    JOIN MonthList ml
        -- Join only if the work session overlaps with the month
        ON eh.WorkStartTime <= ml.MonthEnd
        AND eh.WorkEndTime >= ml.MonthStart
    ORDER BY eh.EmployeeID, ml.MonthStart;
END
GO

Key Details to Adjust #

• Replace YourEmployeeHoursTable: Swap this with your actual table name that stores employee work data.
• Month label formatting: FORMAT(ml.MonthStart, 'MMM yyyy') creates short month names (e.g., Jan 2019). Use 'MMMM yyyy' if you want full names like January 2019.
• Recursion limit: SQL Server defaults to 100 recursive iterations. If your date range spans more than 100 months, add OPTION (MAXRECURSION 0) to the final query to remove this limit.

Example Outputs #

Single-Month Range (2019-01-01 to 2019-01-16) #

Cross-Month Range (2019-01-28 to 2019-02-20) #

内容的提问来源于stack exchange，提问作者PipRon7