如何在Travel Consultant用户的存储过程中为每个用户填充月份列？

It looks like you want to generate a result where each active travel consultant is listed for every month (Jan, Feb, etc.), even if they don't have corresponding sales objective data for that month. Your current query uses a FULL OUTER JOIN, but that won't create the cross-product of users and months you need—instead, we need to explicitly generate all month entries and pair them with each user.

Here's how to modify your stored procedure to achieve this:

Step-by-Step Explanation #

1. Generate a list of all months: We'll use a CTE to create a list of 12 months with their 3-letter abbreviations (matching your example format).
2. Get active users for the profile: Isolate the users who are active and belong to the specified profile.
3. Cross join users with months: This creates every possible combination of user and month, ensuring each user appears for every month.
4. Left join sales data: Attach the sales objective data where it exists for a user-month pair; missing data will show as NULL.

Modified Stored Procedure Code #

ALTER PROCEDURE [dbo].[SP_UserTC_BY__ProfID_FuncID]
    @P_ProfileName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra "rows affected" messages

    -- Validate input parameter
    IF @P_ProfileName IS NULL
        RAISERROR('Null values not allowed for @P_ProfileName', 16, 1);

    -- CTE to create a list of all 12 months with abbreviations
    WITH MonthsList AS (
        SELECT 1 AS MonthNum, 'Jan' AS MonthName
        UNION ALL SELECT 2, 'Feb'
        UNION ALL SELECT 3, 'Mar'
        UNION ALL SELECT 4, 'Apr'
        UNION ALL SELECT 5, 'May'
        UNION ALL SELECT 6, 'Jun'
        UNION ALL SELECT 7, 'Jul'
        UNION ALL SELECT 8, 'Aug'
        UNION ALL SELECT 9, 'Sep'
        UNION ALL SELECT 10, 'Oct'
        UNION ALL SELECT 11, 'Nov'
        UNION ALL SELECT 12, 'Dec'
    ),
    -- CTE to get active users matching the target profile
    ActiveUsers AS (
        SELECT 
            users.UserID, 
            users.Name, 
            prof.ProfileName, 
            funct.Name AS FunctionName
        FROM TBL_User AS users
        JOIN REL_ProfileUser AS relprofileuser ON users.UserID = relprofileuser.UserID
        JOIN TBL_Profile AS prof ON prof.ProfileID = relprofileuser.ProfileID
        JOIN TBL_UserFunction AS funct ON funct.FunctionID = relprofileuser.FunctionID
        WHERE prof.ProfileName = @P_ProfileName AND users.IsActive = 1
    )
    -- Cross join users with months, then left join sales data
    SELECT 
        au.UserID,
        ml.MonthName AS Month,
        au.Name,
        au.ProfileName,
        au.FunctionName,
        salesobj.SalesObjectiveMonth,
        salesobj.GrossMargin,
        salesobj.ReductionWorkingTime,
        salesobj.ConversionRate,
        salesobj.ReductionOnPace
    FROM ActiveUsers au
    CROSS JOIN MonthsList ml
    LEFT JOIN TBL_SalesObjective salesobj 
        ON salesobj.UserID = au.UserID 
        AND MONTH(salesobj.SalesObjectiveMonth) = ml.MonthNum -- Match month number to sales date
    ORDER BY ml.MonthNum, au.UserID; -- Sort to match your example layout
END

Notes #

• Adjusting months: If you only need months that exist in your TBL_SalesObjective table (instead of all 12), replace the hardcoded MonthsList CTE with this dynamic version:

  MonthsList AS (
      SELECT DISTINCT 
          MONTH(SalesObjectiveMonth) AS MonthNum,
          LEFT(DATENAME(MONTH, SalesObjectiveMonth), 3) AS MonthName
      FROM TBL_SalesObjective
  )
  

• Matching logic: If your salesobj.Month column already stores the 3-letter abbreviation (instead of a date), change the join condition to salesobj.Month = ml.MonthName.
• Sorting: The ORDER BY clause ensures results are grouped by month first, then user, which matches your example output.

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