CODEWITHSUNDEEP
c#linqlinq-to-sql
Traffy
Traffy

Reputation: 2861

Counting elements with date values

I've a DB table which is called DBAppointments. This DB is composed of 2 fields : Start Date an End Date. What I'm trying to do is to count how many appointments I have in a certain interval. For example, I have 2 appointments in my table : 

App. 1 : 8h30 -> 10h30
App. 2 : 9h -> 10h

To be more explicit, what I really do is really simple : I want to insert a new appointment, based on a start date and a duration. To perform that, I have to check if I can add an appointment from a certain date and time. Again, an example :

I want to add an appointment which its duration equals to 2 hours. Based on my records, I'll go by step of half-hour to determine if I can or not.

  1. 8h30 to 10h30 : impossible, there is already 2 appointments
  2. 9h to 11h : still the same problem, can not have more than 2 appointments
  3. 9h30 to 11h30 : same situation here
  4. 10h to 12h : I can add a new appointment here as the second is finised !

So, to do that, that's what I'm doing in my code : 

DBAppointment[] appointmentsOfCurrentDay = (from a in context.DBAppointments where a.StartDate.Value.Date == day.Date select a).ToArray();


            foreach (DBAppointment dbapp in appointments)
            {
                DateTime end = dbapp.PatchExpiration.Value;

                for (DateTime startTime = dbapp.StartDate.Value; startTime <= end; startTime = startTime.AddHours(0.5))
                {

                    **int countAppointment = appointmentsOfCurrentDay.Count(a => a.StartDate <= startTime && startTime.AddHours(duration) <= a.EndDate);**

                    if (countAppointment < maxBeds && (Math.Ceiling(Decimal.ToDouble(dbapp.PatchQuantity.Value)) - Decimal.ToDouble(dbapp.PatchQuantity.Value) >= patchQuantity))
                    {
                        IntervalViewModel ivm = new IntervalViewModel();

                        ivm.StartDate = startTime;
                        ivm.EndDate = startTime.AddHours(duration);

                        listValidIntervals.Add(ivm);
                    }
                }

My problem lies in the code line which is in bold. We begin at 8h30 and finish at 10h30, so my count equals to 2 which is correct. But, as we are performing the second iteration (so startime equals to 9h), the count is displaying 1, which is not correct.

I understand why (it is because we say that a.StartDate is greather or equal to startime) but I have no idea about how to fix it.

Upvotes: 0

Views: 61

Answers (3)

Alidou Ouedraogo
Alidou Ouedraogo

Reputation: 51

Just loooking at: int countAppointment = appointmentsOfCurrentDay.Count(a => a.StartDate <= startTime && startTime.AddHours(duration) <= a.EndDate);

It should be: int countAppointment = appointmentsOfCurrentDay.Count(a => a.StartDate <= startTime && a.EndDate >= startTime.AddHours(duration));

Previously you had:

  • first iteration: ivm.StartDate = 8h30 ; ivm.StartDate.AddHours(2) = 10h30

    test : 8h30 <= 8h30 && 10h30 <= 10h30 (true)

  • second iteration: ivm.StartDate = 9h00 ; ivm.StartDate.AddHours(2) = 11h00

    test: 9h00 <= 9h00 && 11h <= 10h00 (false)

Upvotes: 1

Bayeni
Bayeni

Reputation: 1046

I've written this small example to explain my comment, hopefully it will help to resolve your problem. Sorry for being lazy to re-write your code. Let me know if you need further help

   private List<DateTime> ValidEndTime = new List<DateTime>(); //Contains the list of validEndDate 

    private void button1_Click(object sender, EventArgs e)
    {
        List<DateTime> currentAppointment = new List<DateTime>();
        currentAppointment.Add(Convert.ToDateTime("22 May 2014 14:22"));
        currentAppointment.Add(Convert.ToDateTime("22 May 2014 14:30"));

        foreach (DateTime endDate in currentAppointment)
        {
            if (IsAppointmentTimeValid(endDate) == true)
            {
                ValidEndTime.Add(endDate);
            }    
        }     
    }

    private bool IsAppointmentTimeValid(DateTime newAppointmentEndTime)
    {
        //compare max(maxendDateTime) with the newAppointmentDateTime
        return ValidEndTime.Max(t => t.Date) < newAppointmentEndTime? true : false;
    }

Upvotes: 0

st mnmn
st mnmn

Reputation: 3677

I changed my code, Maybe in this way:

int countAppointment = appointmentsOfCurrentDay.Count(a => (startTime >= a.StartDate && startTime < a.EndDate || startTime.AddHours(duration) > a.StartDate && startTime.AddHours(duration) <= a.EndDate) ||
                                                    (a.StartDate >= startTime && a.StartDate < startTime.AddHours(duration) || a.EndDate > startTime && a.EndDate <= startTime.AddHours(duration)));

Upvotes: 0

Related Questions