Linq to SQL - Count number of days in intersection between date ranges

Question

Hey, I have a table that has multiple records with a start date (PlacementDate) and end date (Pulled Date). I'm passing start date and end date parameters into this function which needs to return the records that intersect the start and end date passed in and also specify how many days each record intersects for.

Getting the records that intersect is easy, getting the number of days of intersection between the date ranges is not so easy... Here's what I have so far:

            var query = from d in db.TelemetryDevices
                    join p in db.DevicePlacements on d.TelemetryDeviceID equals p.TelemetryDeviceID
                    where d.CompanyID == companyId && d.BillingPlanID == billingPlanId
                        && (
                        (p.PlacementDate <= startDate && (p.PulledDate != null ? p.PulledDate.Value : DateTime.MaxValue) >= endDate)// { start - end }
                        || (p.PlacementDate <= startDate && (p.PulledDate != null ? p.PulledDate.Value : DateTime.MaxValue) >= startDate)// { start } end
                        || (p.PlacementDate >= startDate && (p.PulledDate != null ? p.PulledDate.Value : DateTime.MaxValue) <= endDate)// start { } end
                        || (p.PlacementDate >= startDate && (p.PulledDate != null ? p.PulledDate.Value : DateTime.MaxValue) >= endDate)// start { end }
                        )
                    select new DeviceView
                    {
                        TelemetryDeviceID = d.TelemetryDeviceID
                    };

Any ideas as to how to solve this would be greatly appreciated.

Answer 1

When you do date arithemtic, .Net implicitely uses TimeSpan types. You can convert a TimeSpan to the number of days. For instance:

 int days = (DateTime.Now.AddDays(10) - DateTime.Now).Days;

will result in 10 days. You just need to figure out in your query which date to use for the comparison (the start or end date, depending on which one is overlapped).

Answer 2

Have a look at this.

If I misunderstood, please let me know.

List<MyClass> dates = new List<MyClass>();
dates.Add(new MyClass { StartDate = new DateTime(2009, 01, 01), EndDate = new DateTime(2009, 01, 08) });
dates.Add(new MyClass { StartDate = new DateTime(2009, 01, 15), EndDate = new DateTime(2009, 01, 20) });
dates.Add(new MyClass { StartDate = new DateTime(2009, 01, 22), EndDate = new DateTime(2009, 01, 31) });
DateTime sDate = new DateTime(2009, 01, 07);
DateTime eDate = new DateTime(2009, 01, 25);
var result = (from d in dates
             orderby d.StartDate
             where !(eDate < d.StartDate || sDate > d.EndDate)
             select new
             {
                 Days = (d.EndDate <= eDate && d.StartDate >= sDate ? d.EndDate.Subtract(d.StartDate).Days:
        sDate >= d.StartDate && sDate <= d.EndDate && eDate >= d.EndDate ? d.EndDate.Subtract(sDate).Days:
                 eDate >= d.StartDate && eDate <= d.EndDate && sDate <= d.StartDate ? eDate.Subtract(d.StartDate).Days:
                 eDate <= d.EndDate && sDate >= d.StartDate ? eDate.Subtract(sDate).Days : 0)
                 }).Sum(x => x.Days);
int total = result;