CODEWITHSUNDEEP
c#linqdatetime
Taco2
Taco2

Reputation: 439

C# Linq Datatable groupby datetime month

I have a datatable that looks like this

enter image description here

The output I'm trying to achieve is described in the picture, where I want to group by the month of the datetime, and agent I suppose. This is what I have so far.

DataTable dtTemp = new DataTable();
dtTemp.Columns.Add("Agent", typeof(string));
dtTemp.Columns.Add("Amount", typeof(decimal));
dtTemp.Columns.Add("Date", typeof(DateTime));

dtTemp = dtTemp.AsEnumerable()
.GroupBy(r => r[0])
.Select(g =>
{
    var row = dt.NewRow();

    row[0] = g.Key;
    row[1] = g.Sum(r => (decimal)r[1]);

    return row;
})
.CopyToDataTable();

Any ideas how to achieve this?

Upvotes: 3

Views: 1513

Answers (3)

Asav Vora
Asav Vora

Reputation: 71

var temp = dtTemp.AsEnumerable().GroupBy(grp => new { 
    grpmonth = Convert.ToDateTime(grp["Date"]).Month, 
    grpyear = Convert.ToDateTime(grp["Date"]).Year, 
    grpagent = grp["Agent"] 
})

.Select(val =>
{
    var row = dtTemp.NewRow();

    row["Agent"] = val.FirstOrDefault()["Agent"];
    row["Amount"] = val.Sum(amt => Convert.ToDecimal(amt["Amount"]));
    row["Date"] = val.FirstOrDefault()["Date"];
    return row;
})
.CopyToDataTable();

Based on this Stack Overflow answer by Arturo Menchaca.

Upvotes: 0

er-sho
er-sho

Reputation: 9771

From analyzing your post, you need to group by data table rows by column Agent and only month and year from column Date.

You need to take one temporary data table dt that can hold data type for each column in your resultant data table result.

So CopyToDataTable() will copy all of your dtTemp group data to new result data table with temporary dt columns data type.

DataTable dt = new DataTable();
dt.Columns.Add("Agent", typeof(string));
dt.Columns.Add("Amount", typeof(decimal));
dt.Columns.Add("Date", typeof(string));


DataTable result = dtTemp.AsEnumerable()
     .Select(x => new
     {
         Agent = x.Field<string>("Agent"),
         Amount = x.Field<decimal>("Amount"),
         Date = x.Field<DateTime>("Date").ToString("MM-yyyy")
     })
     .GroupBy(x => new { x.Agent, x.Date })
     .Select(g =>
     {
         var r = dt.NewRow();

         r["Agent"] = g.Key.Agent;
         r["Amount"] = g.Sum(c => c.Amount);
         r["Date"] = g.FirstOrDefault().Date;

         return r;
     })
     .CopyToDataTable();

Output:

enter image description here

Upvotes: 2

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

So from your input sequence, you want all used Agents, with the total sum of the Amounts per month.

Let's assume your DataTable is a sequence of Rows, and that it easily can be converted to a sequence of Rows:

class RowData
{
    public string Agent {get; set}
    public DateTime Date {get; set;}
    public int Amount {get; set;}
}

IEnumerable<RowData> tableData = ...

The solution if your problem is to make groups of RowData with equal value for Agent, and to group these groups again to make subgroups with equal value for year and month

var AgentsWithAmountsPerMonth = tableData
    .GroupBy(row => row.Agent,        // make groups of rows with same Agent

    // ResultSelector: get the Agent (=key), with all rows that have this Agent
    (agent, rowsWithThisAgent) => new
    {
        Agent = agent,

        // to calculate the totals per year/month, extract the year / month / amount
        TotalsPerMonth = rowsWithThisAgent.Select(row => new
        {
            Year = row.Date.Year,
            Month = row.Date.Month,
            Amount = row.Amount,
        })

        // and group by same Year / Month:
        .GroupBy(row => new {row.Year, row.Month},

            // ResultSelector
            (yearMonth, rowsWithThisYearMonth) => new
            {
                Year = yearMonth.Year,
                Month = yearMonth.Month,
                Total = rowsWithThisYearMont.Select(row => row.Amount).Sum(),

                // Or put the year and month in one field:
                Month = new DateTime(yearMonth.Year, yearMonth.Month, 1),
            },
    });



    });

Upvotes: 0

Related Questions