I have two tables with same number of columns but with data different at few columns.
Example: 1st table data
Apt_ID Res_ID Subject SartTime EndTime Color
0 1 Avail 06-10-2015 08:00:0 06-10-2015 08:30:0 Blue
0 1 Avail 06-10-2015 08:30:0 06-10-2015 09:00:0 Blue
0 2 Avail 06-15-2015 08:00:0 06-15-2015 08:30:0 Black
Result from 2nd tale
Apt_ID Res_ID Subject SartTime EndTime Color
1 1 Booked 06-10-2015 08:00:0 06-10-2015 08:30:0 Blue
2 1 Booked 06-10-2015 08:30:0 06-10-2015 08:40:0 Blue
What I need is:
I need to merge records from both the tables, then replace records in first table with second table records if values at Res_ID , startTime and endTime columns are matched.
If a records from table_2 falls between start and end time slot in table_1,
I need to replace record in table_1 and add a new records for remaining time slot (please refer to second row in table 1 and 2).
Expected result:
Apt_ID Res_ID Subject SartTime EndTime Color
1 1 Booked 06-10-2015 08:00:0 06-10-2015 08:30:0 Blue
2 1 Booked 06-10-2015 08:30:0 06-10-2015 08:40:0 Blue
0 1 Avail 06-10-2015 08:40:0 06-10-2015 09:00:0 Blue
0 2 Avail 06-15-2015 08:00:0 06-15-2015 08:30:0 Black
Hope I make sense, If not please let me know.
I'm new to SQL and I'm trying my best to get this done.So far I have tried using "UNION" but as columns values are not same, records are not replaced and are duplicated. Also I'm working on this by using cursors but want to know if there's a efficient solution.