Search code examples
sqlgroupingrow-number

SQL Row Number with Grouping

I am not sure whether this can be done. I want to group the data based on company with consecutive date. Below is the desired result I am attempting in SQL.

EmpNo Company StartDt EndDt Desired Result
0003 C01 2021-01-01 00:00:00.000 2021-01-10 00:00:00.000 1
0003 C02 2021-01-11 00:00:00.000 2021-01-15 00:00:00.000 2
0003 C02 2021-01-16 00:00:00.000 2021-01-20 00:00:00.000 2
0003 C01 2021-01-21 00:00:00.000 2021-01-31 00:00:00.000 3
Solution

  • You can use lag() to detect when a company changes and then a cumulative sum:

    select t.*,
       sum(case when company = prev_company then 0 else 1 end) over (partition by empno order by startdt) as desired_result
from (select t.*,
             lag(company) over (partition by empno order by startdt) as prev_company
      from t
     ) t