0

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)))
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
deep
  • 41
  • 1
  • 12
  • 1
    When you view the generated SQL in Cognos Report Studio, you can switch it from Cognos SQL to Native SQL. Have you tried this? – Johnsonium May 17 '18 at 16:03
  • You won't be able to easily adapt this for Sql Server 2008 R2. The `Rank()` function is only available in Sql Server 2012 and later. Also, Sql Server 2008 R2 is less than 14 months away from end of life (July 9, 2019). After that point, it will no longer get any updates, not even critical security patches. It's time to start your Sql Server upgrade project now, too. – Joel Coehoorn May 17 '18 at 18:32
  • i know i won't be able to easily adapt this query for SQL server 2008 R2,but still i need to convert it that so can anyone help me with this? thanks – deep May 22 '18 at 11:47

1 Answers1

0

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.

Will
  • 228
  • 1
  • 2
  • 15