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?
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).
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
