sql server - Dense Rank groups -

i have set of data. want produce last field -> d_rank

date  physician  action  d_rank 2016-01-01  dr.john  visit  1 2016-01-01  dr.john  call   1 2016-01-02  dr.john  call   1 2016-01-03  dr.jane  call   2 2016-01-04  dr.jane  call   2 2016-01-05  dr.jane  visit  2 2016-01-06  dr.john  nocall 3 2016-01-07  dr.john  nocall 3 2016-01-08  dr.john  visit  3 2016-01-08  dr.jane  visit  4 2016-01-08  dr.jane  call   4 2016-01-08  dr.joe   visit  5 2016-01-09  dr.joe   call   5 

thanks in advanced.

this isn't using dense_rank(), in sql server 2012+ can use lag() window function determine when physician changes, assign value , sum window function so:

select date, physician, action   , change = sum(change) on (order date, physician desc) (   select *     , change = case when physician = lag(t.physician) on (order t.date, t.physician desc) 0 else 1 end   t   ) s 

rextester demo: http://rextester.com/mqds34484

returns:

+------------+-----------+--------+--------+ |    date    | physician | action | change | +------------+-----------+--------+--------+ | 2016-01-01 | dr.john   | visit  |      1 | | 2016-01-01 | dr.john   | call   |      1 | | 2016-01-02 | dr.john   | call   |      1 | | 2016-01-03 | dr.jane   | call   |      2 | | 2016-01-04 | dr.jane   | call   |      2 | | 2016-01-05 | dr.jane   | visit  |      2 | | 2016-01-06 | dr.john   | nocall |      3 | | 2016-01-07 | dr.john   | nocall |      3 | | 2016-01-08 | dr.john   | visit  |      3 | | 2016-01-08 | dr.jane   | call   |      4 | | 2016-01-08 | dr.jane   | visit  |      4 | | 2016-01-09 | dr.john   | visit  |      5 | | 2016-01-09 | dr.jane   | call   |      6 | | 2016-01-09 | dr.jane   | visit  |      6 | +------------+-----------+--------+--------+ 

expanded sample data day muliple physicians.