none
How to query between two dates, AND each possibility in anohter column must exist RRS feed

  • Question

  • Hi All,

    I am working on a project where I need to find out if all numbers 1 - 8 are used on a day between two dates. Maybe this is not very clear but I will try to explain in more detail.

    Suppose I have 3 columns db. fromDate, toDate, itemNumber.

    - An itemNumber will be 'occupied' between fromDate and toDate.
    - itemNumber can be between 1 and 8 (both included)

    I would like to query db.MyDatas.Where(????). ...... when I give a date say givenDate= '28-07-2011' I want to know if for each number 1..8 a record exists where the fromDate =< givenDate and toDate >= givenDate

    Goal is to check if on givenDate all numbers 1..8 are occupied already or not.

    Anyone have a brilliant idea how to formulate this with a EF query ?

     

    Thursday, July 28, 2011 11:18 AM

Answers

  • Hi Bart;

    The following query should give you what you need.

     

    DateTime dt = DateTime.Parse("7/28/2011");
    
    bool query = (from d in ObjectContext.TableName
      where d.FromDate <= dt && d.ToDate >= dt  
      group d by new {InDateGroup = "InDateGroup"} into dGroup
      select (dGroup.GroupBy(g => g.ItemNumber).Count() == 8)).Single();
    

    if( query ) { Console.WriteLine("0 - 8 where found in the date range"); }

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by bart___s Thursday, July 28, 2011 8:06 PM
    Thursday, July 28, 2011 5:01 PM

All replies

  • You could do a GROUP BY (or equivalent of in linq to entities), group by the field itemNumber, then if that result set has 8 entries in it all 8 numbers must exist in it.

    There is an example of doing grouping here :

    http://msdn.microsoft.com/en-us/library/bb896341.aspx

    Thursday, July 28, 2011 3:26 PM
  • It took me some time to think and read again, but indeed that should be a possibility. After I posted I was thinking to Sum the itemNumber and compare it to the sum of all possible itemNumbers. In my case that probably would work too but its not so flexible.

    I will give this grouping a shot and see if I can get it to work as I want.

    Thursday, July 28, 2011 4:05 PM
  • Hi Bart;

    The following query should give you what you need.

     

    DateTime dt = DateTime.Parse("7/28/2011");
    
    bool query = (from d in ObjectContext.TableName
      where d.FromDate <= dt && d.ToDate >= dt  
      group d by new {InDateGroup = "InDateGroup"} into dGroup
      select (dGroup.GroupBy(g => g.ItemNumber).Count() == 8)).Single();
    

    if( query ) { Console.WriteLine("0 - 8 where found in the date range"); }

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by bart___s Thursday, July 28, 2011 8:06 PM
    Thursday, July 28, 2011 5:01 PM
  • Hi Fernando,

    Great example, thanks a lot. This is very helpful.

     

    I will come back here after I implemented and tested it and will mark the answers.

    Thursday, July 28, 2011 5:04 PM
  • Not a problem.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, July 28, 2011 5:16 PM
  • Ok I got my thing to work... Did it slightly different as I found that the environment was slightly different than I expected when I posted here.

    My goal ws to highligt all days in a 2 month calendar that are fully booked. I expected to get a request per date, but I get one request for the entire two months, so I solved it with a loop (any better solutions are still welcome)

    List<string> fullDates = new List<string>();
    
    for (DateTime dt = dateFrom; dt <= dateTo; dt = dt.AddDays(1))
    {
      var nrOccupied = db.MyDatas
        .Where(m => m.FromDate <= dt && m.ToDate >= dt)
        .GroupBy(m => m.itemNumber)
        .Count();
              
      if (nrOccupied >= 8)
      {
        fullDates.Add(dt.ToShortDateString());
      }
    }
    

    I needed to check >=8 because when an occupied period finishes, at the same dat I can have a new period starting.

    The way this is used... db.MyDates.Where().GroupBy().Count() .... does this structure has a specific name ?

     

    Thursday, July 28, 2011 8:06 PM