SQL Server : multiply column with alias name in select list

Question

I have a select list in my query like this:

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
    MIN(t.Title) AS Title,
    t.ItemID, ' + @selectedColumn + ' as SelectedColumnSales ' + ', 
    t.CurrentPrice,
    (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem
FROM 
    dbo.SearchedUserItems t

The part of the query that I'm having problem with is the following:

  (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem

In the select list... The "SelectedColumnSales" can be a different column based on what I pass into the query like following:

DECLARE @selectedColumn NVARCHAR(500)

    IF(@SelectedRange=7)
       SET @selectedColumn = 't.SevenDaySales'
    ELSE IF (@SelectedRange=14)
       SET @selectedColumn='t.FourteenDaySales'
    ELSE IF (@SelectedRange=21)
       SET @selectedColumn='t.TwentyOneDaySales'
    ELSE IF (@SelectedRange=30)
       SET @selectedColumn='t.ThirtyDaySales'

Now to get revenue per item column I need to multiply these two like above, but the query throws this error:

Inner exception: SqlException: Invalid column name 'SelectedColumnSales'.

How can I multiply the dynamic column with the static column in the select list?

Can someone help me out?

Answer 1

if you're writing a dynamic query, which it looks like you are, you can just put the variable in your equation

 declare @sql nvarchar(max)
 set @sql = 'select 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        ,' + @selectedColumn + ' as SelectedColumnSales ' + 
        ', t.CurrentPrice
         , (t.CurrentPrice * ' + @selectedColumn + ') as TotalRevenuePerItem

    FROM 
        dbo.SearchedUserItems t'
exec(@sql)