Creating trend data from data that has start and e

Question

I'm using C#, NHibernate, Fluent NHibernate and LINQ (repository pattern).

Fictional story on what I want to do

Say I have a car parking place, so I have a car that is renting a space from date until date. This becomes one entry into the database.

I would like to find out how many cars there were for each day over the period of last 30 days. So data that can be used in a trend chart.

Description

What I have is entries in the database that are like so:

Entry:
--------
ID
StartDate
EndDate

I need to find out how many entries there were each day for a time period (say last 30 days).

If I have say this data in the database:

ID | Start      | End
---|------------|----------
1  | 2013-01-01 | 2013-01-01
2  | 2013-01-02 | 2013-01-02
3  | 2013-01-02 | 2013-01-03 <-- NOTICE
4  | 2013-01-03 | 2013-01-03
5  | 2013-01-03 | 2013-01-05 <-- NOTICE
6  | 2013-01-04 | 2013-01-05

I have date range 2013-01-01 until 2013-01-05.

I except the result:

Date       | Value | Entry IDs (not part of the result, only here for clarity)
-----------|-------|-----------
2013-01-01 | 1     | 1
2013-01-02 | 2     | 2 3
2013-01-03 | 3     | 3 4 5
2013-01-04 | 2     | 5 6
2013-01-05 | 2     | 5 6

I currently only group by startdate and then get the numbers, but this is invalid now because of this new requirement that was added with enddate.

I could do a for each day in that range, try to find entries that match in that, but I'm thinking that would be too expensive on the database or web service.

Advices?

Answer 1

You can use the Time Period Library for .NET to calculate the trend data:

// ----------------------------------------------------------------------
class CarPeriod : DayTimeRange
{

  // --------------------------------------------------------------------
  public CarPeriod( int id, DateTime start, DateTime end ) :
    this( id, start, end.Date.AddDays( 1 ).Subtract( start.Date ).Days )
  {
  } // CarPeriod

  // --------------------------------------------------------------------
  public CarPeriod( int id, DateTime start, int days ) :
    base( start.Year, start.Month, start.Day, days )
  {
    Id = id;
  } // CarPeriod

  // --------------------------------------------------------------------
  public int Id { get; private set; }

} // class CarPeriod

// ----------------------------------------------------------------------
[Sample( "DaysTrendData" )]
public void DaysTrendData()
{
  // periods
  ITimePeriodCollection carPeriods = new TimePeriodCollection();
  carPeriods.Add( new CarPeriod( 1, new DateTime( 2013, 1, 1 ), new DateTime( 2013, 1, 1 ) ) );
  carPeriods.Add( new CarPeriod( 2, new DateTime( 2013, 1, 2 ), new DateTime( 2013, 1, 2 ) ) );
  carPeriods.Add( new CarPeriod( 3, new DateTime( 2013, 1, 2 ), new DateTime( 2013, 1, 3 ) ) );
  carPeriods.Add( new CarPeriod( 4, new DateTime( 2013, 1, 3 ), new DateTime( 2013, 1, 3 ) ) );
  carPeriods.Add( new CarPeriod( 5, new DateTime( 2013, 1, 3 ), new DateTime( 2013, 1, 5 ) ) );
  carPeriods.Add( new CarPeriod( 6, new DateTime( 2013, 1, 4 ), new DateTime( 2013, 1, 5 ) ) );

  Days testDays = new Days( new DateTime( 2013, 1, 1 ), 5 );
  foreach ( Day testDay in testDays.GetDays() )
  {
    Console.Write( "Day: " + testDay + ": " );
    foreach ( CarPeriod carPeriod in carPeriods )
    {
      if ( carPeriod.IntersectsWith( testDay ) )
      {
        Console.Write( carPeriod.Id + " " );
      }
    }
    Console.WriteLine();
  }
} // DaysTrendData

Answer 2

Maybe something like this:

var start = new DateTime(2013, 01, 01);
var end = DateTime(2013, 01, 05);

var q = d.Query()
         .Where(x =>
             x.Date >= start
             && end >= x.Date) // there you filter for range 
         .GroupBy(x => x.Date); // use .Day to group by day

Start time and end time are inclusive.

EDIT

I've found this (actually i've a similar case in my project):

var start = new DateTime(2013, 1, 1);
var end = new DateTime(2013, 1, 5);
var current = start;
var ranges = new List<DateTime>();

while (current <= end)
{
    ranges.Add(current);

    current = current.AddDays(1);
}

var res = ranges.Select(x => new
{
    date = x,
    entries = entries.Where(e => e.Start <= x && x <= e.End).ToList()
}).ToList();

Try test it and maybe you can acomodate to your case (note that filtering starts by the date range).