如何修改存储过程变量，实现不同idContainerIndex的价格计算结果求和

Got it, let's sort out this variable assignment issue for you! The problem with your current code is that you haven't wrapped the subqueries in a structure that lets you add their results directly. Here's the correct syntax for both variables, with clear explanations:

1. Correct @Container_3Month_Avg Assignment #

You need to treat each average calculation as a scalar subquery (one that returns a single value), then add them together inside a single SELECT statement. I also added ISNULL handling to avoid getting a NULL total if one of the subqueries has no matching data:

DECLARE @Container_3Month_Avg money = (
    SELECT 
        -- Calculate average for idContainerIndex = 1
        ISNULL((
            SELECT AVG(cntr.[PricePerContainer])
            FROM plx.[Part_v_Part_e]
            OUTER APPLY (
                SELECT TOP 3 [PricePerContainer]
                FROM [mtx].[ContainerIndexPrice] 
                WHERE [idContainerIndex] = 1
                  AND [DateDeactivated] IS NULL
                ORDER BY [DateMonthStart] DESC
            ) cntr
        ), 0)
        +
        -- Calculate average for idContainerIndex = 14
        ISNULL((
            SELECT AVG(inbd.[PricePerContainer])
            FROM plx.[Part_v_Part_e]    
            OUTER APPLY (
                SELECT TOP 3 [PricePerContainer]
                FROM [mtx].[ContainerIndexPrice] 
                WHERE [idContainerIndex] = 14
                  AND [DateDeactivated] IS NULL
                ORDER BY [DateMonthStart] DESC
            ) inbd
        ), 0)
)

How this works: #

• Each inner (SELECT AVG(...)) is a scalar subquery that outputs one number (the average for that specific ID).
• ISNULL(..., 0) ensures if a subquery returns NULL (no data found for the ID), it contributes 0 to the sum instead of breaking the total.
• The + operator adds the two results together, and the outer SELECT passes that sum to assign to your variable.

2. Correct @Container_Last Assignment #

The same logic applies here—add the two TOP 1 results, with optional ISNULL safety:

DECLARE @Container_Last money = (
    SELECT 
        ISNULL((
            SELECT TOP 1 [PricePerContainer]
            FROM [mtx].[ContainerIndexPrice] 
            WHERE [idContainerIndex] = 1
              AND [DateDeactivated] IS NULL
            ORDER BY [DateMonthStart] DESC
        ), 0)
        +
        ISNULL((
            SELECT TOP 1 [PricePerContainer]
            FROM [mtx].[ContainerIndexPrice] 
            WHERE [idContainerIndex] = 14
              AND [DateDeactivated] IS NULL
            ORDER BY [DateMonthStart] DESC
        ), 0)
)

Quick Cleanup Notes: #

• I removed the redundant 1 = 1 from your WHERE clauses—they don't affect the query and just add unnecessary noise.
• If you're 100% sure both idContainerIndex values will always have active data, you can skip the ISNULL calls, but including them makes your code more resilient to edge cases.

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