Menu
I have a datatable with two columns FromDate and ToDate , which are in string format.
I want to check if there are any duplicate records in my table.i.e
From Date To Date
----------------------
9/01/2012 9/16/2012
8/23/2012 8/24/2012
8/25/2012 8/25/2012
8/5/2012 8/6/2012
8/26/2012 8/27/2012
9/15/2012 9/23/2012
The table contains duplicate records as their date range is mapping for
From Date To Date
----------------------
9/01/2012 9/16/2012
9/15/2012 9/23/2012
It should return false.
354
You can do this by swapping the ranges if necessary up front. Then, you can detect overlap if the second range start is: less than or equal to the first range end (if ranges are inclusive, containing both the start and end times); or. less than (if ranges are inclusive of start and exclusive of end).
SQL Query using Lag Function for OverLapping Time Intervals SQL programmers now can use SQL LAG() function to compare all table rows with the previous row when ordered by ID column.
let's pick the big date SELECT ID, EMP_ID, [START DATE], MAX(END DATE) FROM (SELECT ID, EMP_ID, TEAM, [END DATE], MIN([START DATE]) [START DATE] FROM my_table GROUP BY ID, EMP_ID, END_DATE ) a GROUP BY ID, EMP_ID, [START DATE] -- Now we are done with similar end date and similar start date -- At this point I will write ...
The basic idea is 1) first take input_start to test_start (if both of them are not equal and input_start is min) 2) always take test_start and test_end 3) take test_end to input_end if test_end is less than input end (and end_input and end_test are not equal).
var query = from row in dt.AsEnumerable()
from row1 in dt.AsEnumerable()
where
(
(
DateTime.Parse(row1.Field<string>("fromDate")) >= DateTime.Parse(row.Field<string>("fromDate")) &&
DateTime.Parse(row1.Field<string>("fromDate")) <= DateTime.Parse(row.Field<string>("toDate"))
)
||
(
DateTime.Parse(row1.Field<string>("toDate")) >= DateTime.Parse(row.Field<string>("fromDate")) &&
DateTime.Parse(row1.Field<string>("toDate")) <= DateTime.Parse(row.Field<string>("toDate"))
)
)
select new
{
fromDate = DateTime.Parse(row1.Field<string>("fromDate")),
toDate = DateTime.Parse(row1.Field<string>("toDate"))
};
//This lst contains the dates which are overlapping
var lst = query.Distinct().ToList();
Okay then, a selfjoin will help here:
I have a small class TimePeriod, just to meet your needs
public class TimePeriod
{
public int Id;
public DateTime FromDate { get; set; }
public DateTime ToDate { get; set; }
public static DateTime Parse(string date)
{
var dt = DateTime.Parse(date,
CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.RoundtripKind);
return dt;
}
}
then I have some TestData
var list = new List();
list.Add(new TimePeriod() { Id = 1, FromDate = TimePeriod.Parse("9/01/2012"), ToDate = TimePeriod.Parse("9/16/2012") });
list.Add(new TimePeriod() { Id = 2, FromDate = TimePeriod.Parse("8/23/2012"), ToDate = TimePeriod.Parse("8/24/2012") });
list.Add(new TimePeriod() { Id = 3, FromDate = TimePeriod.Parse("8/25/2012"), ToDate = TimePeriod.Parse("8/25/2012") });
list.Add(new TimePeriod() { Id = 4, FromDate = TimePeriod.Parse("8/5/2012"), ToDate = TimePeriod.Parse("8/6/2012") });
list.Add(new TimePeriod() { Id = 5, FromDate = TimePeriod.Parse("8/26/2012"), ToDate = TimePeriod.Parse("8/27/2012") });
list.Add(new TimePeriod() { Id = 6, FromDate = TimePeriod.Parse("9/15/2012"), ToDate = TimePeriod.Parse("9/23/2012") });
And here is the solution: (with some inspiration of OraNob, thanks for that)
var overlaps = from current in list
from compare in list
where
(
(compare.FromDate > current.FromDate &&
compare.FromDate < current.ToDate) ||
(compare.ToDate > current.FromDate &&
compare.ToDate < current.ToDate)
)
select new
{
Id1 = current.Id,
Id2 = compare.Id,
};
Perhaps you want to leave out the second Id (as you will have duplicates here ( 1 / 6) and (6 / 1)
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
