palak mehta palak mehta - 3 months ago 14
SQL Question

Check if a table contains overlapping timespans

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.

Answer
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();
Comments