how to calculate dense_rank() as per the row order

Question

I came with a problem which looked easy but I could not get the desired output. My sample data is as follows : -

end_date_row    row_code
2010-06-30        12
2011-06-30        12
2012-06-30        12
2013-06-30        12
2014-06-30        12
2014-07-16        12
2014-12-31        18
2015-06-30        18
2015-07-06        18
2015-11-17        12
NULL              18

I want the output as -

end_date_row    row_code    rn
2010-06-30         12        1
2011-06-30         12        1
2012-06-30         12        1
2013-06-30         12        1
2014-06-30         12        1
2014-07-16         12        1
2014-12-31         18        2
2015-06-30         18        2
2015-07-06         18        2
2015-11-17         12        3
NULL               18        3

Below is the code that brought me close but I still could not get the exact output.

select end_date_row,row_code,rn1
from
(
select end_date_row,row_code,row_number() over (order by (select 1)) as rn, row_number() over (order by (select 1)) - row_number() over (partition by row_code order by (end_date_row)) as rn1
from a
) as abc
order by rn

My output from the above code is -

end_date_row  row_code  rn1
2010-06-30  12  0
2011-06-30  12  0
2012-06-30  12  0
2013-06-30  12  0
2014-06-30  12  0
2014-07-16  12  0
2014-12-31  18  5
2015-06-30  18  5
2015-07-06  18  5
2015-11-17  12  3
 NULL       18  10

I can not use lead/lag functions as I am using sql server 2008r2. Can we have a solution to this problem without recursive cte's or while loop?

Answer 1

Not sure but this may do it

select end_date_row, row_code 
     , dense_rank() over (order by rnA - rnP) as rn
from ( select end_date_row, row_code 
            , row_number() over (                      order by end_date_row) as rnA
            , row_number() over (partition by row_code order by end_date_row) as rnP
         from a 
     ) tt 

Answer 2

You can use the following query:

SELECT end_date_row, row_code, 
       SUM(COALESCE(flag, 0)) OVER 
       (ORDER BY COALESCE(end_date_row, '9999-12-31')) + 1
FROM (
  SELECT end_date_row, row_code,
         CASE WHEN row_code <> 
                   LAG(row_code) OVER 
                   (ORDER BY COALESCE(end_date_row, '9999-12-31')) 
            THEN 1 END AS flag       
  FROM a) AS t

Demo here

If you want trailing NULL values in end_date_row field to be treated as 'no change' then use this one:

SELECT end_date_row, row_code, 
       SUM(COALESCE(flag, 0)) OVER 
       (ORDER BY COALESCE(end_date_row, '9999-12-31')) + 1
FROM (
  SELECT end_date_row, row_code,
         CASE WHEN end_date_row IS NOT NULL AND
                   row_code <> 
                   LAG(row_code) OVER 
                   (ORDER BY COALESCE(end_date_row, '9999-12-31')) 
            THEN 1 END AS flag       
  FROM a) AS t

Demo here

In SQL-Server 2008 you can use:

SELECT end_date_row, row_code, 
       DENSE_RANK() OVER (ORDER BY COALESCE(group_date, '9999-12-31'))
FROM (
  SELECT end_date_row, row_code, 
         MIN(end_date_row) OVER (PARTITION BY grp) AS group_date
  FROM (
    SELECT end_date_row, row_code,
         ROW_NUMBER() OVER (ORDER BY COALESCE(end_date_row, '9999-12-31')) -
         ROW_NUMBER() OVER (PARTITION BY row_code 
                            ORDER BY COALESCE(end_date_row, '9999-12-31')) AS grp
    FROM a) AS t) AS s

Demo here