question

manojmathe avatar image
manojmathe 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.
sqlserverduplicate values
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ok. I did not get the question. are you trying to find the data between when somebody started and when somebody ended???
0 Likes 0 ·

1 Answer

·
Jon Crawford avatar image
Jon Crawford answered
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 ) )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.