t-sql repeat row numbers within group-CodePudding

I need to create an ID for every time a name changes in the task history. The rank needs to do restart with each task and step.

The closest I got to my goal is using the code below.

But it does not produce correct result for when a person appears again in the historical list of actions.

DENSE_RANK() OVER (ORDER BY TaskName, Person)

Thanks in advance

CodePudding user response：

You can use lag() to see where a person changes. Then use a cumulative sum:

select t.*,
       sum(case when prev_person = person then 0 else 1 end) over
           (partition by task_name order by timestamp) as desired_output
from (select t.*,
             lag(person) over (partition by task_name order by timestamp) as prev_person
      from t
     ) t ;

Note: I am interpreting your question as your wanting the numbers separately for each task ("every time a name changes in the task history").

EDIT:

Based on your comment:

select t.*,
       sum(case when prev_person = person and prev_stop_name = step_name then 0 else 1 end) over
           (partition by task_name order by timestamp) as desired_output
from (select t.*,
             lag(person) over (partition by task_name order by timestamp) as prev_person,
             lag(step_name) over (partition by task_name order by timestamp) as prev_step_name
      from t
     ) t ;