how to convert a query from cognos sql to t-sql

Question

I have a report which is running on Cognos and that we are migrating to SSRS. The Cognos SQL uses the rank function. Can someone please help me to figure out how I can write the same query in T-SQL:

rank() over (at "left"(upper(cast_char(TIMESTAMP '2018-04-30 00:00:00.0')), 3), dim_customer.concern_name 
order by XSUM(case when (cast(dim_order_date.order_date_date as date) between 
CAST(:PQ2 AS timestamp) and CAST(:PQ1 AS timestamp)) 
then invoice_lines_fact3.fare_taken else 0 end   
at "left"(upper(cast_char(TIMESTAMP '2018-04-30 00:00:00.0')),3),dim_customer.concern_name,invoice_lines_fact3.inv_line_id  
for "left"(upper(cast_char(TIMESTAMP '2018-04-30 00:00:00.0')),3),dim_customer.concern_name ) desc nulls last)  as  Rank1,`

and also for this where clause filter:

((cast(dim_order_date.order_date_date as date) between _add_years(cast(TIMESTAMP '2018-04-01 00:00:00.0' as date),(-1)) and CAST(:PQ1 AS timestamp))
   and (((dim_route.AIRLCD in ('CC','AA')) and (dim_route.END_DATE < CAST(:PQ2 AS timestamp))) 
   or (dim_route.END_DATE is NULL)))

Answer 1

You rank() function is available in 2008.

Having looked at your code you have a "XSUM" command.

Sorting this out should be your first point of call. Below is a link to a solution (on stack).

Converting Cognos reports to SSRS , what does XSUM command does?

Potentially, fixing this will allow your rank function to work.