I am currently attempting to edit a pair of variables that are part of a larger stored procedure. These variables are currently set up like so:

DECLARE @Container_3Month_Avg money = (

SELECT AVG(cntr.[PricePerContainer])
FROM plx.[Part_v_Part_e]
    OUTER APPLY (

    SELECT TOP 3 [PricePerContainer]
    FROM [mtx].[ContainerIndexPrice] 
    WHERE 1 = 1
        AND [idContainerIndex] = 1
        AND [DateDeactivated] IS NULL
    ORDER BY [DateMonthStart] DESC

    ) cntr
)

DECLARE @Container_Last money = (

    SELECT TOP 1 [PricePerContainer]
    FROM [mtx].[ContainerIndexPrice] 
    WHERE 1 = 1
        AND [idContainerIndex] = 1
        AND [DateDeactivated] IS NULL
    ORDER BY [DateMonthStart] DESC

)

What I would like to do is modify these variables to be a little more complicated. [idContainerIndex] = 1 represents a subset of the data. I need to modify the code so that it combines the [PricePerContainer] values for two different [idContainerIndex] into a single variable. For instance, I need @Container_3Month_Avg to be the SUM of the AVG of the TOP 3 [PricePerContainer] for [idContainerIndex] = 1 and [idContainerIndex] = 14. The same applies for @Container_Last, I need this to be the SUM of the TOP 1 [PricerPerContainer] for [idContainerIndex] = 1 and [idContainerIndex] = 14.

This is just about as far as I got:

           DECLARE @Container_3Month_Avg money = (

           **fbx01avg   fbx01-ipiavg**

                    (
            SELECT AVG(cntr.[PricePerContainer])
            FROM plx.[Part_v_Part_e]
                OUTER APPLY (

                SELECT TOP 3 [PricePerContainer]
                FROM [mtx].[ContainerIndexPrice] 
                WHERE 1 = 1
                    AND [idContainerIndex] = 1
                    AND [DateDeactivated] IS NULL
                ORDER BY [DateMonthStart] DESC

                ) cntr
            ) fbx01avg
            
            (
            SELECT AVG(inbd.[PricePerContainer])
            FROM plx.[Part_v_Part_e]    
                OUTER APPLY (

                SELECT TOP 3 [PricePerContainer]
                FROM [mtx].[ContainerIndexPrice] 
                WHERE 1 = 1
                    AND [idContainerIndex] = 14
                    AND [DateDeactivated] IS NULL
                ORDER BY [DateMonthStart] DESC

                ) inbd
            ) fbx01-ipiavg
        )

fbx01avg fbx01-ipiavg => how do I write this bit of code?

    DECLARE @Container_Last money = (

            **fbx01   fbx01-ipi**
            
            (
            SELECT TOP 1 [PricePerContainer]
            FROM [mtx].[ContainerIndexPrice] 
            WHERE 1 = 1
                AND [idContainerIndex] = 1
                AND [DateDeactivated] IS NULL
            ORDER BY [DateMonthStart] DESC
            ) fbx01
            
            (
            SELECT TOP 1 [PricePerContainer]
            FROM [mtx].[ContainerIndexPrice] 
            WHERE 1 = 1
                AND [idContainerIndex] = 14
                AND [DateDeactivated] IS NULL
            ORDER BY [DateMonthStart] DESC
            ) fbx01-ipi

            )

fbx01 fbx01-ipi => how do I write this bit of code?

The bolded text is really what I'm looking to accomplish in simple terms. I just need to add these two select statements together somehow to get a single return for each variable. Any assistance would be greatly appreciated.

CodePudding user response：

Why not just:

DECLARE @Container_3Month_Avg money = (

                    (
            SELECT AVG(cntr.[PricePerContainer])
            FROM plx.[Part_v_Part_e]
                OUTER APPLY (

                SELECT TOP 3 [PricePerContainer]
                FROM [mtx].[ContainerIndexPrice] 
                WHERE 1 = 1
                    AND [idContainerIndex] = 1
                    AND [DateDeactivated] IS NULL
                ORDER BY [DateMonthStart] DESC

                ) cntr
            ) 
             
            (
            SELECT AVG(inbd.[PricePerContainer])
            FROM plx.[Part_v_Part_e]    
                OUTER APPLY (

                SELECT TOP 3 [PricePerContainer]
                FROM [mtx].[ContainerIndexPrice] 
                WHERE 1 = 1
                    AND [idContainerIndex] = 14
                    AND [DateDeactivated] IS NULL
                ORDER BY [DateMonthStart] DESC

                ) inbd
            ) 
        )

Your actual queries are kinda weird though. You do an average of cntr.[PricePerContainer], but there's no condition between [Part_v_Part_e] and [PricePerContainer]

What's the purpose of plx.[Part_v_Part_e] at all.

CodePudding user response：

I think this may have worked:

SELECT SUM(avgPricePerContainer) as avgPricePerContainer
    FROM
    (
        (
        SELECT AVG(cntr.[PricePerContainer]) as avgPricePerContainer 
        FROM plx.[Part_v_Part_e]
            OUTER APPLY (

            SELECT TOP 3 [PricePerContainer]
            FROM [mtx].[ContainerIndexPrice] 
            WHERE 1 = 1
                AND [idContainerIndex] = 1
                AND [DateDeactivated] IS NULL
            ORDER BY [DateMonthStart] DESC

            ) cntr
        )
       union all
        (
        SELECT AVG(inbd.[PricePerContainer]) as avgPricePerContainer
        FROM plx.[Part_v_Part_e]    
            OUTER APPLY (

            SELECT TOP 3 [PricePerContainer]
            FROM [mtx].[ContainerIndexPrice] 
            WHERE 1 = 1
                AND [idContainerIndex] = 14
                AND [DateDeactivated] IS NULL
            ORDER BY [DateMonthStart] DESC

            ) inbd
        )
    ) s