SQL Row Number with Grouping-CodePudding

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

CodePudding user response：

Something like:

SELECT * FROM `<your-table>`
GROUP BY `Company`
ORDER BY `StartDt` DESC

CodePudding user response：

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