运输行业SQL需求：同一司机多DriverCode里程合并汇总

Hey Matt, great question—this is a common issue with employee ID rotations, and we can fix it by grouping your data at the driver level instead of the individual DriverCode level. Let's break this down step by step.

The Core Problem #

Right now, your query groups by DriverCode, which splits the same driver's mileage across multiple rows when they get a new ID after an internal transfer. We need to first map all related DriverCodes to a single driver identity, then sum their combined mileage.

Solution 1: Group by Driver Identity (Name + Safeguards) #

Since you're using name to identify duplicate DriverCodes, we can use a CTE to create a "driver group" for each unique name, then aggregate mileage across all DriverCodes in that group.

Here's the adjusted query:

WITH DriverGroups AS (
    -- Create a group for each driver, using their name as the key
    -- We'll pick one DriverCode as the "primary" (e.g., the earliest one by hire date)
    SELECT
        mpp_lastfirst AS DriverName,
        mpp_id AS DriverCode,
        mpp_hiredate AS HireDate,
        ROW_NUMBER() OVER (PARTITION BY mpp_lastfirst ORDER BY mpp_hiredate ASC) AS RowNum
    FROM manpowerprofile
    WHERE mpp_terminationdt > GETDATE() AND mpp_id <> 'UNKNOWN'
)

SELECT
    -- Use the first DriverCode in the group as the display ID
    dg.DriverCode,
    dg.DriverName,
    dg.HireDate,
    SUM(s.stp_lgh_mileage) AS TotMiles
FROM stops s (NOLOCK)
INNER JOIN legheader lh (NOLOCK) ON lh.lgh_number = s.lgh_number
INNER JOIN manpowerprofile m (NOLOCK) ON m.mpp_id = lh.lgh_driver1
-- Link each DriverCode to its driver group
INNER JOIN DriverGroups dg ON dg.DriverName = m.mpp_lastfirst
WHERE
    lh.lgh_outstatus = 'CMP'
    AND m.mpp_terminationdt > GETDATE()
    AND m.mpp_id <> 'UNKNOWN'
-- Group by the driver's identity instead of individual DriverCode
GROUP BY dg.DriverCode, dg.DriverName, dg.HireDate
HAVING SUM(s.stp_lgh_mileage) > 850000
ORDER BY dg.DriverCode DESC

Solution 2: Pre-Aggregate Mileage for Better Performance #

If your dataset is large, pre-aggregating mileage per DriverCode first can reduce the number of joins and improve query speed:

WITH DriverCodeMiles AS (
    -- Calculate mileage for each individual DriverCode first
    SELECT
        lh.lgh_driver1 AS DriverCode,
        m.mpp_lastfirst AS DriverName,
        m.mpp_hiredate AS HireDate,
        SUM(s.stp_lgh_mileage) AS DriverTotal
    FROM stops s (NOLOCK)
    INNER JOIN legheader lh (NOLOCK) ON lh.lgh_number = s.lgh_number
    INNER JOIN manpowerprofile m (NOLOCK) ON m.mpp_id = lh.lgh_driver1
    WHERE
        m.mpp_terminationdt > GETDATE()
        AND m.mpp_id <> 'UNKNOWN'
        AND lh.lgh_outstatus = 'CMP'
    GROUP BY lh.lgh_driver1, m.mpp_lastfirst, m.mpp_hiredate
),
DriverGroups AS (
    -- Create a group for each driver, picking a primary ID and consistent hire date
    SELECT
        DriverName,
        MIN(DriverCode) AS PrimaryDriverCode, -- Use MIN/MAX to pick a representative ID
        MIN(HireDate) AS HireDate -- Use earliest hire date for accuracy
    FROM DriverCodeMiles
    GROUP BY DriverName
)

SELECT
    dg.PrimaryDriverCode AS DriverCode,
    dg.DriverName,
    dg.HireDate,
    SUM(dcm.DriverTotal) AS TotMiles
FROM DriverCodeMiles dcm
INNER JOIN DriverGroups dg ON dcm.DriverName = dg.DriverName
GROUP BY dg.PrimaryDriverCode, dg.DriverName, dg.HireDate
HAVING SUM(dcm.DriverTotal) > 850000
ORDER BY dg.PrimaryDriverCode DESC

Critical Notes to Avoid Mistakes #

• Duplicate Names: If your company has drivers with identical names, this will incorrectly merge their mileage. Check if manpowerprofile has a unique, non-changing identifier (like an employee ID or SSN) and use that instead of mpp_lastfirst for grouping—it's far more reliable.
• Hire Date Consistency: If a driver's new DriverCode has a different HireDate, use MIN(HireDate) to keep their original hire date in the final output.
• NOLOCK Usage: Be aware that NOLOCK can return uncommitted data. Only use it if your business can tolerate potential inconsistencies in the report.

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