CODEWITHSUNDEEP
c#linqsql-server-2008
Alan
Alan

Reputation: 253

Getting results based on a timeframe and grouping by month

I have a database with a number of tables. One of the tables VersionBuild contains a date column. I get results using the following query and only select records that appear within a certain timeframe (eg. the last 6 months)

The code I am using is as follows

DateTime startDate = DateTime.Now.AddMonths(-(timeFrame));
var grouped = db.VersionBuilds
                .Where(r => r.product_id == id && r.date > startDate)
                .GroupBy(r => new { r.date.Year, r.date.Month })
                .Select(r => new { 
                    month = getMonthName(r.Key.Month), 
                    totalIssues = r.Sum(zz => zz.AcrolinxReport.total_issues) 
                });
return grouped;

The one problem with the code is that if there are no records for a particular month, it ignores it. I am displaying the returned information in a chart so this leaves a gap in the data. For example if I have records in the month of May and June, it will return data for may and june but what I want is to return data for all months in the specified timeframe, regardless of if there is any data for them or not.

Upvotes: 0

Views: 188

Answers (2)

Jason Boyd
Jason Boyd

Reputation: 7029

I believe this will work for you. It was difficult to test without data but I think you should not have any problems.

// Assume 'timeframe' is a positive integer.

// Get the start date.
DateTime startDate = DateTime.Now.AddMonths(-timeframe);

// Generate a list of months (year and month, actually) beginning 
// with the month of 'startDate'.
var months =
    Enumerable
    .Range(0, timeframe)
    .Select(x => startDate.AddMonths(x))
    .Select(x => new { x.Year, x.Month });

// Create builds query.
var builds =
    db.VersionBuilds
    .Where(r => r.product_id == id && r.date > startDate)
    .GroupBy(r => new { r.date.Year, r.date.Month })
    .Select(r => new { 
        Month = r.Key.Month,
        Year = r.Key.Year,
        TotalIssues = r.Sum(zz => zz.AcrolinxReport.total_issues));

// Join 'months' on 'builds'
var grouped =
    months
    .GroupJoin(
        builds.AsEnumerable(),
        date => date,
        build => new { build.Year, build.Month },
        (date, buildGroup) => new { 
            date.Year, 
            date.Month, 
            TotalIssues = buildGroup.Sum(x => x.TotalIssues) })
    .OrderBy(x => x.Year)
    .ThenBy(x => x.Month)
    .Select(x => new { Month = getMonth(x.Month), x.TotalIssues });

Upvotes: 1

Christos
Christos

Reputation: 53958

You could try something like this:

First, we create a list with all months in the given time frame For instance, if we run this now and set as a timeFrame to be 3, then This list would contain the following datetimes

  • 2015-08-15 ....,
  • 2015-07-15 ....,
  • 2015-06-15

Here is the code:

var monthsInTimeFrame = new List<DateTime>();
for(int i = timeFrame; i>=0 ; i--)
{ 
    monthsInTimeFrame.Add(DateTime.Now.AddMonths(-i));
}

Then you keep fetching your data the way you have already dome, but with a minor change. Instead of creating a list of objects of an anonymous type, you create a list of objects of a named typed. I picked the vague name Data. You could had picked whatever name you want. Furthermore, this type would have two extra properties one the Year and the other the Month. We need them, in order to identify if a specific month from the expected months are in the list. 

DateTime startDate = DateTime.Now.AddMonths(-(timeFrame));
var grouped = db.VersionBuilds
                .Where(r => r.product_id == id && r.date > startDate)
                .GroupBy(r => new { r.date.Year, r.date.Month })
                .Select(r => new Data
                { 
                    Year = r.Key.Name,
                    Month = r.Key.Month,
                    MonthName = getMonthName(r.Key.Month), 
                    TotalIssues = r.Sum(zz => zz.AcrolinxReport.total_issues) 
                }).ToList();

Notice that we have to call the ToList at the end of our query, because below we will make use of the Add method to add the missing months.

Last, we have to loop through the items of the monthsInTimeFrame:

foreach(var month in monthsInTimeFrame)
{
    // If there isn't any item in the list called
    // grouped with the same year and month of the current month,
    // we have to add a new item in this list       
    if(!grouped.Any(item=>item.Year==month.Year &&
                          item.Month==month.Month)) 
        grouped.Add(new Data
        { 
            Year = month.Year, 
            Month = month.Month,
            MonthName = getMonthName(month.Month), 
            TotalIssues = 0 
        });
}
return grouped;

The definition of the Data class could have been this:

public class Data
{
    public int Year { get; set; }
    public int Month { get; set; }
    public string MonthName { get; set; }
    public int TotalIssues { get; set; }
}

Upvotes: 1

Related Questions