SQLTeam.com | Weblogs | Forums

How to get data from two tables by removing duplicates based on few column values

sql2008

#1

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.


#2

select Apt_ID, Res_ID, Subject, startTime, EndTime, Color
from table2
union
select Apt_ID, Res_ID, Subject, startTime, EndTime, Color
from table1 a
where not exists( select *
from table2 b
where a.Res_ID = b.Res_ID
and a.startTime = b.startTime
and a.EndTime = b.EndTime
)


#3

Thanks Viggnesh for the solution, It works partially, except for the part where the result does not show the records need to be split.
For example in table_1 I have an appointment record available from 8.30 - 9.00 , where as in table_2 I have an appointment booked from 8.30 - 8.40. So in end result I need to show booked appointment from 8.30 - 8.40 and available appointment from 8.40 - 9.00. Please refer to second and third rows in expected result


#4

This is the logic to retrieve the details. As per your business requirement you can change it.