How to solve this sql problem with an analytic function.
I have the following data
DATE PRIMARY SECONDARY
2018-10-22 18:51:10 1 2
2018-10-23 18:51:10 1 2
2018-10-24 18:51:10 1 2
2018-10-25 18:51:10 2 1
2018-10-26 18:51:10 2 1
2018-10-27 18:51:10 2 1
2018-10-28 18:51:10 2 1
2018-10-29 18:51:10 1 2
2018-10-30 18:51:10 1 2
2018-10-31 18:51:10 1 2
2018-11-01 18:51:10 1 2
2018-11-02 18:51:10 2 1
2018-11-03 18:51:10 1 2
2018-11-04 18:51:10 1 2
2018-11-05 18:51:10 1 2
2018-11-06 18:51:10 1 2
Basically it is a date with a host id that was the primary at that time and who the secondary was. I am trying to write a sql statement that will provide the following output.
| Start | End | Primary | Secondary |
|---|---|---|---|
| 2018-10-22 18:51:10 | 2018-10-25 18:51:10 | 1 | 2 |
| 2018-10-25 18:51:10 | 2018-10-29 18:51:10 | 2 | 1 |
| 2018-10-29 18:51:10 | 2018-11-02 18:51:10 | 1 | 2 |
| 2018-11-02 18:51:10 | 2018-11-03 18:51:10 | 2 | 1 |
| 2018-11-03 18:51:10 | NULL | 1 | 2 |
I cannot figure out how to group the rows together!