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
x
| 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.
Advertisement
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 ) AReturns
Employee work_period Cont_Days1 2019-01-01 11 2019-01-02 21 2019-01-03 31 2019-01-04 41 2019-01-05 51 2019-01-10 11 2019-01-11 21 2019-01-12 32 2019-01-20 12 2019-01-22 12 2019-01-23 22 2019-01-24 3