Data Binning with SQL or Linq

Question

I am creating a custom reporting app in C# and need to aggregate some data.

Let's make it simple. Say I have 10,000 sales for a year, I need a generic way to bin the data by say, month, day or hour. Each bin would therefore sum all the sales within that period.

Has anyone ever written a query like this in either SQL or Linq?

Answer 1

Hey,

Traditionally, it would be:

(from s in Sales
where s.SalesDate.Year = 2009
&& s.SalesDate.Month = 2
select s.Amount).Sum();

Now, you can make that flexible like:

.. GetData(TimePeriod period)
{
return (from s in Sales
  where
  (
     //timeframe is enum of month, day, hour
     period.TimeFrame == TimeFrame.Month 
     && s.SalesDate.Year == period.year
     && s.SalesDate.Month == period.Month
  )
  ||
  (
     period.TimeFrame == TimeFrame.Day
     && s.SalesDate.Year == period.Year
     && s.SalesDate.Month == period.Month
     && s.SalesDate.Day == period.Day
  )
  ||
  (
    ..
  )
  select s);

}

The only thing I'm not sure of is whether you can use an enum for evaluation; you may be able to add booleans for each condition:

bool isMonth = (period.TimeFrame == Timeframe.Month);

and alter the query so that you use isMonth && instead of period.TimeFrame == Timeframe.Month.

That's about as flexible as you can get. The only other way to be even more flexible is to manually loop through each record to aggregate data, and create a generic method for that...

Hope that all makes sense :-)