Formula to Divide Total of Columns and Format Result Not Valid-CodePudding

I have the following SQL code below that is returning an error message stating that the AvgPYs column is not valid. This is also impacting my Auth column. Is there an issue with my formula? Any help is appreciated.

SELECT [tblCeiling].[Proj Code], [tblCeiling].[Act Code], [tblCeiling].[Cost Ctr], [tblCeiling].[Date], [tblCeiling].[Ref2], [tblCeiling].[Analyst], [tblCeiling].[Type], [tblCeiling].[B or O], [tblCeiling].[Jul], [tblCeiling].[Aug], 
                  [tblCeiling].[Sep], [tblCeiling].[Oct], [tblCeiling].[Nov], [tblCeiling].[Dec], [tblCeiling].[Jan], [tblCeiling].[Feb], [tblCeiling].[Mar], [tblCeiling].[Apr], [tblCeiling].[May], [tblCeiling].[Jun], [tblCeiling].[Perm], [tblCeiling].[Temp], [tblCeiling].[LimitedTerm], 
                  [tblCeiling].[LTDate], [tblCeiling].[Sal_Rate], [tblCeiling].[New], [Perm]   [Temp]   [LimitedTerm] AS Monthly, Format(([tblCeiling].[Jul]   [tblCeiling].[Aug]   [tblCeiling].[Sep]   [tblCeiling].[Oct]   [tblCeiling].[Nov]   [tblCeiling].[Dec]   [tblCeiling].[Jan]  
                  [tblCeiling].[Feb]   [tblCeiling].[Mar]   [tblCeiling].[Apr]   [tblCeiling].[May]   [tblCeiling].[Jun]) / 12, '0.0####') AS AvgPYs, 
                  ((([Sal_Rate] * [AvgPYs]) * 1000) / 1000) AS Auth, [Dollar Adj]   [Auth] AS Budget, [tblCeiling].[Import], [tblCeiling].[Dollar Adj], [tblCeiling].[OngoingOrOneTime], [tblCeiling].[OneTimeEndingDate]
FROM     (SELECT DISTINCT *
                  FROM      [tblactcode]) AS [tblactcode] RIGHT JOIN
                      (SELECT DISTINCT *
                       FROM      [tblCeiling]) AS [tblCeiling] ON [tblactcode].[Act Code] = [tblCeiling].[Act Code]
WHERE  ((([tblCeiling].[Jul]) = iif([jul] IS NULL, 0, [jul])) AND (([tblCeiling].[Aug]) = iif([aug] IS NULL, 0, [aug])) AND (([tblCeiling].[Sep]) = iif([sep] IS NULL, 0, [sep])) AND (([tblCeiling].[Oct]) = iif([oct] IS NULL, 0, [oct])) AND (([tblCeiling].[Nov]) = iif([nov] IS NULL,
                   0, [nov])) AND (([tblCeiling].[Dec]) = iif([dec] IS NULL, 0, [dec])) AND (([tblCeiling].[Jan]) = iif([jan] IS NULL, 0, [jan])) AND (([tblCeiling].[Feb]) = iif([feb] IS NULL, 0, [feb])) AND (([tblCeiling].[Mar]) = iif([mar] IS NULL, 0, [mar])) AND (([tblCeiling].[Apr]) 
                  = iif([apr] IS NULL, 0, [apr])) AND (([tblCeiling].[May]) = iif([may] IS NULL, 0, [may])) AND (([tblCeiling].[Jun]) = iif([jun] IS NULL, 0, [jun])) AND (([tblCeiling].[Import]) = 0))
ORDER BY [tblCeiling].[Proj Code], [tblCeiling].[Cost Ctr], [tblCeiling].[Date]

Here is the specific line I'm referring to:

Format(([tblCeiling].[Jul]   [tblCeiling].[Aug]   [tblCeiling].[Sep]   [tblCeiling].[Oct]   [tblCeiling].[Nov]   [tblCeiling].[Dec]   [tblCeiling].[Jan]  
                  [tblCeiling].[Feb]   [tblCeiling].[Mar]   [tblCeiling].[Apr]   [tblCeiling].[May]   [tblCeiling].[Jun]) / 12, '0.0####') AS AvgPYs,

CodePudding user response：

The specific issue you have run into is attempting to use a calculation in the same scope you are calculating it - which isn't possible. You can only access a calculated value in an outer query.

Or a neat solution is to use CROSS APPLY which allows you to reuse a calculation as follows. In general this is done as:

select -- existing columns before AvgPYs
  , AvgPYs
  -- , some formula which depends on AvgPYs 
from (
    -- existing query
) C -- C is an acceptable short alias for Ceiling
cross apply (
    values (formula)
) X (AvgPYs)

In your case I think the following is correct:

SELECT C.[Proj Code], C.[Act Code], C.[Cost Ctr], C.[Date], C.[Ref2], C.[Analyst], C.[Type], C.[B or O], C.[Jul], C.[Aug] 
    , C.[Sep], C.[Oct], C.[Nov], C.[Dec], C.[Jan], C.[Feb], C.[Mar], C.[Apr], C.[May], C.[Jun], C.[Perm], C.[Temp], C.[LimitedTerm]
    , C.[LTDate], C.[Sal_Rate], C.[New], [Perm]   [Temp]   [LimitedTerm] AS Monthly
    , X.AvgPYs 
    , Y.Auth
    , [Dollar Adj]   Y.Auth AS Budget, C.[Import], C.[Dollar Adj], C.[OngoingOrOneTime], C.[OneTimeEndingDate]
FROM (
    SELECT DISTINCT *
    FROM [tblactcode]
) AS AC
RIGHT JOIN (
    SELECT DISTINCT *
    FROM [tblCeiling]
) AS C ON AC.[Act Code] = C.[Act Code]
CROSS APPLY (
    VALUES (Format((C.[Jul]   C.[Aug]   C.[Sep]   C.[Oct]   C.[Nov]   C.[Dec]   C.[Jan]  
    C.[Feb]   C.[Mar]   C.[Apr]   C.[May]   C.[Jun]) / 12, '0.0####'))
) AS X (AvgPYs)
CROSS APPLY (
    VALUES (((([Sal_Rate] * X.AvgPYs) * 1000) / 1000))
) Y (Auth)
WHERE (((C.[Jul]) = iif([jul] IS NULL, 0, [jul])) AND ((C.[Aug]) = iif([aug] IS NULL, 0, [aug])) AND ((C.[Sep]) = iif([sep] IS NULL, 0, [sep])) AND ((C.[Oct]) = iif([oct] IS NULL, 0, [oct])) AND ((C.[Nov]) = iif([nov] IS NULL,
    0, [nov])) AND ((C.[Dec]) = iif([dec] IS NULL, 0, [dec])) AND ((C.[Jan]) = iif([jan] IS NULL, 0, [jan])) AND ((C.[Feb]) = iif([feb] IS NULL, 0, [feb])) AND ((C.[Mar]) = iif([mar] IS NULL, 0, [mar])) AND ((C.[Apr]) 
    = iif([apr] IS NULL, 0, [apr])) AND ((C.[May]) = iif([may] IS NULL, 0, [may])) AND ((C.[Jun]) = iif([jun] IS NULL, 0, [jun])) AND ((C.[Import]) = 0)
)
ORDER BY C.[Proj Code], C.[Cost Ctr], C.[Date];

Note: A key purpose of a table alias is to have a short reference to the table. See how much easier it is to read with shorter aliases.