asked
Rretrieve data without overlapping datetimes
I need some thoughts to the best implementation of this case I have data where there can be multiple values with start & end datetime, now i need to pull the data without overlapping the dates, below is the sample data. CREATE TABLE table2 ( start_date DATE NOT NULL, end_date DATE NOT NULL, comments VARCHAR(100) NULL , id int ); INSERT INTO table2 (start_date, end_date, id) VALUES ('2011-12-01', '2012-01-02', 5), ('2012-01-01', '2012-01-06', 5), ('2012-01-05', '2012-01-10', 5), ('2012-01-09', '2012-01-11', 5); from this i need the data which is not overlapping for each id ('2011-12-01', '2012-01-02', 5), ('2012-01-05', '2012-01-10', 5) Adding resoning for each row Sorry if my question was confusion few, adding more details here This is the expected result set. ('2011-12-01', '2012-01-02', 5), ('2012-01-01', '2012-01-06', 5), -- cannot pick this as it overlaps with the first record, the startdate -- is in between the first record start and enddate ('2012-01-05', '2012-01-10', 5), -- we will pick this as we skipped the second record claiming that it -- overlaps with first record ('2012-01-09', '2012-01-11', 5); -- this get skipped as it overlaps with the above record Please share me the thoughts on what cane be the best way to implement this ? Thanks for the support Thanks, Manoj.
ok. I did not get the question. are you trying to find the data between when somebody started and when somebody ended???
You are arbitrarily deciding that the first and third records are not overlaps, when in truth all four records overlap with each other. What is your criteria for deciding which one to keep and which to drop? If you wanted to just get the first date that overlaps and the last date that overlaps, you could do the following, but since they all overlap it doesn't match your "expected result set": IF Object_id('tempdb..#table2') IS NULL BEGIN CREATE TABLE #table2 ( start_date DATE NOT NULL, end_date DATE NOT NULL, comments VARCHAR(100) NULL , id int ); INSERT INTO #table2 (start_date, end_date, id) VALUES ('2011-12-01', '2012-01-02', 5), ('2012-01-01', '2012-01-06', 5), ('2012-01-05', '2012-01-10', 5), ('2012-01-09', '2012-01-11', 5); END SELECT min(start_date),max(end_date) FROM #table2 externalTable WHERE exists( SELECT * FROM #table2 internalTable WHERE
externalTable.ID=
internalTable.ID AND ( internalTable.start_date<=externalTable.end_date OR internalTable.end_date>=externalTable.start_date ) )
19 People are following this question.
