I'm trying to find how many days people have continuously worked in SQL. I'm thinking a rolling sum might be the solution but don't know how to work it out.
My sample data is
| Employee | work_period |
| 1 | 2019-01-01 |
| 1 | 2019-01-02 |
| 1 | 2019-01-03 |
| 1 | 2019-01-04 |
| 1 | 2019-01-05 |
| 1 | 2019-01-10 |
| 1 | 2019-01-11 |
| 1 | 2019-01-12 |
| 2 | 2019-01-20 |
| 2 | 2019-01-22 |
| 2 | 2019-01-23 |
| 2 | 2019-01-24 |
The designated result should be
| Employee | work_period | Continuous Days |
| 1 | 2019-01-01 | 1 |
| 1 | 2019-01-02 | 2 |
| 1 | 2019-01-03 | 3 |
| 1 | 2019-01-04 | 4 |
| 1 | 2019-01-05 | 5 |
| 1 | 2019-01-10 | 1 |
| 1 | 2019-01-11 | 2 |
| 1 | 2019-01-12 | 3 |
| 2 | 2019-01-20 | 1 |
| 2 | 2019-01-22 | 1 |
| 2 | 2019-01-23 | 2 |
| 2 | 2019-01-24 | 3 |
If the days are not continuous, the continuous counting will re-start from 1.
Just another option ... Very similar to a Gaps-and-Islands, but without the final aggregation.
Example
Select Employee
,work_period
,Cont_Days = row_number() over (partition by Employee,Grp Order by Work_Period)
From (
Select *
,Grp = datediff(day,'1900-01-01',work_period) - row_number() over (partition by Employee Order by Work_Period)
From YourTable
) A
Returns
Employee work_period Cont_Days
1 2019-01-01 1
1 2019-01-02 2
1 2019-01-03 3
1 2019-01-04 4
1 2019-01-05 5
1 2019-01-10 1
1 2019-01-11 2
1 2019-01-12 3
2 2019-01-20 1
2 2019-01-22 1
2 2019-01-23 2
2 2019-01-24 3