Sql-server – Use Aliases In Calculations

sql serversql-server-2008-r2t-sql

In SQL Server 2008 I am attempting to run the below calculation, but I am getting an error of

Msg 207, Level 16, State 1, Line 11
Invalid column name 'Rpt1'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Rpt2'.

This is the syntax I am trying to execute.

Declare @saleID varchar(100), @totalsales int

Set @totalsales = (Select COUNT(numsales) 
                  FROM OPENQUERY(444.222.333.123, 'Select 
                                                   numsales, saleID 
                                                   FROM saledate')
                  WHERE saleID = @saleID)

Select
Rpt1 = count(case when empID = '1931' Then 1 else 0 end)
,Rpt2 = count(case when empID = '1831' Then 1 else 0 end)
,Balance = @totalsales-Rpt1-Rpt2
From totalsaleinfo
Where saleid = @saleid

Best Answer

You will need to store the rpt1 and rpt2 as variables then subtrack from total, not as the aliases.

Or perform:

Select
@totalsales-
(count(case when empID = '1931' Then 1 else 0 end))
-
(count(case when empID = '1831' Then 1 else 0 end))
as Balance
From totalsaleinfo

Where saleid = @saleid

Related Question