How do I make row_number () work after a case clause?-CodePudding

I'm having issues with applying this row_number() function on SQL Server. Ideally, I want to use the function, dense_rank() but only works when writing on Oracle.

When I run the query I get the SQL Error[207][S0001]: Invalid column name 'tag_year'.

    select 
'last 7 days' as time_period,
CAST(dd.CalendarDate as DATE) as 'Date',
dd.CalendarID,
cast(CAST(dd.CalendarDate as DATE) as varchar) as time,
right(CAST(dd.CalendarDate as DATE), 5) as axis_date,
(case
    when dd.CalendarDate between dateadd(month, -11,getdate()) and getdate() then 'cy'
    else 'ly' end) as tag_year,
'DAY'   CAST(
ROW_NUMBER () over (partition by tag_year order by time desc ) as varchar) as time_index, --calendar week day
ROW_NUMBER() over (partition by tag_year order by time desc ) as last_n,
CAST(last_n AS varchar) time_period as time_key
from dim.Calendar dd
where 
(dd.CalendarDate between getdate() - 7 and getdate() - 1) -- last 31 days
or (dd.CalendarDate between getdate() - 7 - 52 * 7 and getdate() - 1 - 52 * 7);

Not sure what the issue is and when I add group by, it crashes stil.

Any help or guidance would be appreciated

CodePudding user response：

tag_year is the result of an expression created inline in the query. To use its value on another expression, you'll need to create this value as a column in a subquery first.

For example:

select *,
  'DAY'   CAST(
  ROW_NUMBER () over (partition by tag_year order by time desc ) as varchar)
    as time_index, --calendar week day
  ROW_NUMBER() over (partition by tag_year order by time desc ) as last_n,
from (
  select 
    'last 7 days' as time_period,
    CAST(dd.CalendarDate as DATE) as 'Date',
    dd.CalendarID,
    cast(CAST(dd.CalendarDate as DATE) as varchar) as time,
    right(CAST(dd.CalendarDate as DATE), 5) as axis_date,
    case 
      when dd.CalendarDate between dateadd(month, -11,getdate()) and getdate() 
      then 'cy'
      else 'ly'
    end as tag_year,
    CAST(last_n AS varchar) time_period as time_key
  from dim.Calendar dd
  where 
  (dd.CalendarDate between getdate() - 7 and getdate() - 1) -- last 31 days
  or (dd.CalendarDate between getdate() - 7 - 52 * 7 and getdate() - 1 - 52 * 7)
) x