How to get a continuous date interval from rows fulfilling specific condition?
I have a table of employees states with 2 types of user_position. The interval is continuous if the next rowhigher date_position per user_id has the same user_id, the next day value and user_position didn't change. The user cannot have different user positions in one day.
Have a feeling it requires several cases, window functions and tsrange, but can't quite get the right result.
I would be really grateful if you could help me.
Fiddle:
http://sqlfiddle.com/#!17/ba641/1/0
The result should look like this:
| user_id | user_position | position_start | position_end |
|---|---|---|---|
| 1 | 1 | 01.01.2019 | 02.01.2019 |
| 1 | 2 | 03.01.2019 | 04.01.2019 |
| 1 | 1 | 05.01.2019 | 06.01.2019 |
| 2 | 1 | 01.01.2019 | 03.01.2019 |
| 2 | 2 | 04.01.2019 | 05.01.2019 |
| 2 | 2 | 08.01.2019 | 08.01.2019 |
| 2 | 2 | 10.01.2019 | 10.01.2019 |
Create/insert query for the source data:
CREATE TABLE IF NOT EXISTS users_position ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id integer, user_position integer, date_position date); INSERT INTO users_position (user_id, user_position, date_position) VALUES (1, 1, '2019-01-01'), (1, 1, '2019-01-02'), (1, 2, '2019-01-03'), (1, 2, '2019-01-04'), (1, 1, '2019-01-05'), (1, 1, '2019-01-06'), (2, 1, '2019-01-01'), (2, 1, '2019-01-02'), (2, 1, '2019-01-03'), (2, 2, '2019-01-04'), (2, 2, '2019-01-05'), (2, 2, '2019-01-08'), (2, 2, '2019-01-10'); 
