All Forums Database
a2kz 16 posts Joined 05/13
07 Jan 2016
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|Confli ct_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
 
 
 
 

You must sign in to leave a comment.