Conflicting Timestamp values in Start_ts and End_ts

Database
Enthusiast

Conflicting Timestamp values in Start_ts and End_ts

Hi All,

Below is the ipnut data and desired output 

create table table1 (

Store_id char(3),

emp_id demical(18,0),

store_segment Varchar(40),

Shift_start_ts Timestamp(0),

Shift_end_ts Timestamp(0)

) primary index(emp_id);

insert into table1 values ('R001',100,'A','2016-01-01 09:00:00','2016-01-01 14:00:00');

insert into table1 values ('R001',100,'A','2016-01-01 10:00:00','2016-01-01 11:00:00');

insert into table1 values ('R002',100,'B','2016-01-01 10:00:00','2016-01-01 11:00:00');

insert into table1 values ('R001',100,'A','2016-01-01 08:00:00','2016-01-01 09:00:00');

insert into table1 values ('R001',100,'A','2016-01-01 15:00:00','2016-01-01 16:00:00');

insert into table1 values ('R001',100,'B','2016-01-01 12:00:00','2016-01-01 13:00:00');

insert into table1 values ('R003',100,'C','2016-01-01 12:30:00','2016-01-01 12:45:00');

insert into table1 values ('R004',100,'D','2016-01-01 13:00:00','2016-01-01 16:00:00');

DESIRED OUTPUT

Store_ID|Emp_Id|Store_Segment|Conflict_Start_ts|Conflict_end_ts

R001|100|A|2016-01-01 10:00:00|2016-01-01 11:00:00

R001|100|A|2016-01-01 10:00:00|2016-01-01 11:00:00

R002|100|B|2016-01-01 10:00:00|2016-01-01 11:00:00

R001|100|A|2016-01-01 12:00:00|2016-01-01 13:00:00

R001|100|B|2016-01-01 12:00:00|2016-01-01 13:00:00

R001|100|A|2016-01-01 12:30:00|2016-01-01 12:45:00

R001|100|B|2016-01-01 12:30:00|2016-01-01 12:45:00

R003|100|C|2016-01-01 12:30:00|2016-01-01 12:45:00

R003|100|C|2016-01-01 12:30:00|2016-01-01 12:45:00

R001|100|A|2016-01-01 13:00:00|2016-01-01 14:00:00

R004|100|D|2016-01-01 13:00:00|2016-01-01 14:00:00

R001|100|A|2016-01-01 15:00:00|2016-01-01 16:00:00

R004|100|D|2016-01-01 15:00:00|2016-01-01 16:00:00

Business Requirements:

1. The conflict table should have the overlapping/conflicting shift for an employee.

2. The overlapping shift might be across a single store or accross multiple stores.

I tried with the examples of the releated date ovaerlapping post but the output format here is bit different and not able to achieve the exact output. Would appreacite if anyonce could please suggest something.

Thanks,

ambuj