Rolling Sum when date is continuous

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.

Answer

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