I'm trying to create a nice complex query..and I've run into so many issues that at this point, i'm giving it to you people.
This is the query..it's formated for notpad..it may wrap here, if it does I'm sorry.
I get a 'Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Commission_Mult'.
When I try to run it...please help
-- Commission Query
SELECT [First Name],
[Paid On Date],
[Employees].[Name] As "Sale Rep",
-- Eligibility Of Commission: 'Eligible', 'Un-Eligible'
Eligibility = (CASE
WHEN (dbo.GetBusinessDays([Paid On Date], GetDate()) >= 14) Then 'Eligible'
-- Commission Multiplier: 0, 1, -1
Commission_Mult = CASE
WHEN [Status] = 2 AND [pd_commission] = 0 AND [rt_commission] = 0 THEN 1
WHEN [Status] != 2 AND [pd_commission] = 1 AND [rt_commission] = 0 THEN -1
-- Commission Amount
[Commission]*Commission_Mult As "Commission",
-- Status Name: Paid, Returned, Cancelled, Ordered
WHEN [Status] = 1 THEN 'Ordered'
WHEN [Status] = 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
WHEN [Status] > 2 THEN 'Cancelled'
WHEN [Status] != 2 And [pd_commission] = 0 And [rt_commission] = 0 THEN 'Paid'
End As "Status_Name"
INNER JOIN Employees On [Account Rep] = [Id Number]
INNER JOIN Products On [AntiTel].[Product Id] = [Products].[Product Id]
WHERE ([pd_commission] = 0 AND [rt_commission] = 0) OR ([pd_Commission] = 1 AND [rt_commission] = 1)