Linq - Group by week in datatable

[Radha Shyam]

I have a datatable which comprises values from 1st January to march, something like this:

DataTable datatable = new DataTable("Employee");
datatable.Columns.Add("Date", typeof(string));
datatable.Columns.Add("Employee", typeof(string));
datatable.Columns.Add("Job1", typeof(double));
datatable.Columns.Add("Job2", typeof(double));

datatable.Rows.Add(new Object[] { "1/4/2013", "A", 1.3, 2 });
datatable.Rows.Add(new Object[] { "1/4/2013", "B", 2.5, 6 });
datatable.Rows.Add(new Object[] { "1/6/2013", "C", 3.7, 2.4 });
datatable.Rows.Add(new Object[] { "1/7/2013", "D", 11, 0.0 });
datatable.Rows.Add(new Object[] { "1/7/2013", "F", 334, 0 });
datatable.Rows.Add(new Object[] { "1/8/2013", "A", 1.87, 1 });
datatable.Rows.Add(new Object[] { "1/8/2013", "B", 6.85, 2 });
datatable.Rows.Add(new Object[] { "1/9/2013", "C", 58, 226 });
datatable.Rows.Add(new Object[] { "1/16/2013", "A", 9.43, 1.45 });
datatable.Rows.Add(new Object[] { "1/16/2013", "B", 5.27, 0.6 });
datatable.Rows.Add(new Object[] { "1/22/2013", "C", 45.4, 5 });
datatable.Rows.Add(new Object[] { "1/23/2013", "A", 44, 4.78 });
datatable.Rows.Add(new Object[] { "1/29/2013", "B", 45, 40 });
datatable.Rows.Add(new Object[] { "2/2/2013", "C", 45, 54.12 });
datatable.Rows.Add(new Object[] { "2/2/2013", "D", 7, 4.4587 });
datatable.Rows.Add(new Object[] { "2/3/2013", "F", "265", 11.486 });
datatable.Rows.Add(new Object[] { "3/3/2013", "A", "25", 28.124 });

The result should look like this for Job1:

Employee 1/7-1/13 1/14-1/20 1/21-1/27 1/28-2/3 and so on...
A sum of values for this 7 days
B
C
D

I want to add all the values of Job1 for each employee in a time range which is one week starting from monday to sunday.
Please suggest how can I group the Job1 values week wise and store them in another datatable.

[vijay6]

Hey Radha Shyam, try this code (I changed datatype of 'Date' field to 'DateTime' from 'string' and Table name to 'EmployeeTable' from 'Employee' because already you've a field called as 'Employee' to save employee names in your code)

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {   
        DataTable datatable;

        public Form1()
        {
            InitializeComponent();
        }
		
        private void Form1_Load(object sender, EventArgs e)
        {
            DateTime StartDate = new DateTime(2012, 12, 31); // 12/31/2012
            DateTime EndDate = new DateTime(2013, 3, 3); // 03/03/2013

            DateTime StartWeekDate = StartDate;
            DateTime EndWeekDate = StartWeekDate.AddDays(6);

            datatable = new DataTable("EmployeeTable");
            datatable.Columns.Add("Date", typeof(DateTime));
            datatable.Columns.Add("Employee", typeof(string));
            datatable.Columns.Add("Job1", typeof(double));
            datatable.Columns.Add("Job2", typeof(double));

            datatable.Rows.Add(new Object[] { "1/4/2013", "A", 1.3, 2 });
            datatable.Rows.Add(new Object[] { "1/4/2013", "B", 2.5, 6 });
            datatable.Rows.Add(new Object[] { "1/6/2013", "C", 3.7, 2.4 });
            datatable.Rows.Add(new Object[] { "1/7/2013", "D", 11, 0.0 });
            datatable.Rows.Add(new Object[] { "1/7/2013", "F", 334, 0 });
            datatable.Rows.Add(new Object[] { "1/8/2013", "A", 1.87, 1 });
            datatable.Rows.Add(new Object[] { "1/8/2013", "B", 6.85, 2 });
            datatable.Rows.Add(new Object[] { "1/9/2013", "C", 58, 226 });
            datatable.Rows.Add(new Object[] { "1/16/2013", "A", 9.43, 1.45 });
            datatable.Rows.Add(new Object[] { "1/16/2013", "B", 5.27, 0.6 });
            datatable.Rows.Add(new Object[] { "1/22/2013", "C", 45.4, 5 });
            datatable.Rows.Add(new Object[] { "1/23/2013", "A", 44, 4.78 });
            datatable.Rows.Add(new Object[] { "1/29/2013", "B", 45, 40 });
            datatable.Rows.Add(new Object[] { "2/2/2013", "C", 45, 54.12 });
            datatable.Rows.Add(new Object[] { "2/2/2013", "D", 7, 4.4587 });
            datatable.Rows.Add(new Object[] { "2/3/2013", "F", 265, 11.486 });
            datatable.Rows.Add(new Object[] { "3/3/2013", "A", 25, 28.124 });

            for (DateTime currentDate = StartWeekDate; currentDate <= EndDate; )
            {
                calculate(currentDate, EndWeekDate, "A");
                calculate(currentDate, EndWeekDate, "B");
                calculate(currentDate, EndWeekDate, "C");
                calculate(currentDate, EndWeekDate, "D");
                calculate(currentDate, EndWeekDate, "F");

                currentDate = currentDate.AddDays(7);
                EndWeekDate = currentDate.AddDays(6);
            }
        }

        private void calculate(DateTime StartWeekDate, DateTime EndWeekDate, string name)
        {
            List<TempClass> list = new List<TempClass>();
			
			Console.WriteLine(name + "     " + StartWeekDate.ToShortDateString() + "     " + EndWeekDate.ToShortDateString() + "     ");

            Console.WriteLine(datatable.AsEnumerable().Select(a => new TempClass
            {
                Date = a.Field<DateTime>("Date"),
                Employee = a.Field<string>("Employee"),
                Job1 = a.Field<double>("Job1"),
                Job2 = a.Field<double>("Job2")
            }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job1).ToString());

            Console.WriteLine(datatable.AsEnumerable().Select(a => new TempClass
            {
                Date = a.Field<DateTime>("Date"),
                Employee = a.Field<string>("Employee"),
                Job1 = a.Field<double>("Job1"),
                Job2 = a.Field<double>("Job2")
            }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job2).ToString());
        }
    }

	public class TempClass
    {
        public DateTime Date
        {
            get;
            set;
        }

        public string Employee
        {
            get;
            set;
        }
        public double Job1
        {
            get;
            set;
        }
        public double Job2
        {
            get;
            set;
        }
    }
}

[Radha Shyam]

Thank you for your reply but this does not solve my problem. I want to have columns where week starts from 1/7 - 1/13 and in that column the values for Job1 is aggregated for this particular week. I want this all stored in a datatable as i have to do further calculation with these values so no writing on consoles. Anyhow, appreciate your help. Thanks!

[vijay6]

Hey Radha Shyam, i thought you may write that simple part of the code by yourself. Check the following code, i modified it for you. TextBox 'textbox' is for your reference, if you want you can remove it.

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        DataTable datatable;
        TextBox textbox;
        DataTable GroupByWeek;
        List<TempClass> list;
        double j1, j2;

        public Form1()
        {
            InitializeComponent();
        }
		
        private void Form1_Load(object sender, EventArgs e)
        {
            DateTime StartDate = new DateTime(2012, 12, 31);
            DateTime EndDate = new DateTime(2013, 3, 3);

            DateTime StartWeekDate = StartDate;
            DateTime EndWeekDate = StartWeekDate.AddDays(6);

            datatable = new DataTable("EmployeeTable");
            datatable.Columns.Add("Date", typeof(DateTime));
            datatable.Columns.Add("Employee", typeof(string));
            datatable.Columns.Add("Job1", typeof(double));
            datatable.Columns.Add("Job2", typeof(double));

            datatable.Rows.Add(new Object[] { "1/4/2013", "A", 1.3, 2 });
            datatable.Rows.Add(new Object[] { "1/4/2013", "B", 2.5, 6 });
            datatable.Rows.Add(new Object[] { "1/6/2013", "C", 3.7, 2.4 });
            datatable.Rows.Add(new Object[] { "1/7/2013", "D", 11, 0.0 });
            datatable.Rows.Add(new Object[] { "1/7/2013", "F", 334, 0 });
            datatable.Rows.Add(new Object[] { "1/8/2013", "A", 1.87, 1 });
            datatable.Rows.Add(new Object[] { "1/8/2013", "B", 6.85, 2 });
            datatable.Rows.Add(new Object[] { "1/9/2013", "C", 58, 226 });
            datatable.Rows.Add(new Object[] { "1/16/2013", "A", 9.43, 1.45 });
            datatable.Rows.Add(new Object[] { "1/16/2013", "B", 5.27, 0.6 });
            datatable.Rows.Add(new Object[] { "1/22/2013", "C", 45.4, 5 });
            datatable.Rows.Add(new Object[] { "1/23/2013", "A", 44, 4.78 });
            datatable.Rows.Add(new Object[] { "1/29/2013", "B", 45, 40 });
            datatable.Rows.Add(new Object[] { "2/2/2013", "C", 45, 54.12 });
            datatable.Rows.Add(new Object[] { "2/2/2013", "D", 7, 4.4587 });
            datatable.Rows.Add(new Object[] { "2/3/2013", "F", 265, 11.486 });
            datatable.Rows.Add(new Object[] { "3/3/2013", "A", 25, 28.124 });

            textbox = new TextBox();
            textbox.Width = 250;
            textbox.Height = 200;
            textbox.Multiline = true;
            textbox.ScrollBars = ScrollBars.Vertical;
            textbox.Location = new Point(12, 12);
            this.Controls.Add(textbox);

            textbox.Text = String.Empty;

            GroupByWeek = new DataTable("GroupByWeek");
            GroupByWeek.Columns.Add("Employee", typeof(string));
            GroupByWeek.Columns.Add("From", typeof(DateTime));
            GroupByWeek.Columns.Add("To", typeof(DateTime));
            GroupByWeek.Columns.Add("Job1", typeof(double));
            GroupByWeek.Columns.Add("Job2", typeof(double));

            for (DateTime currentDate = StartWeekDate; currentDate <= EndDate; )
            {
                calculate(currentDate, EndWeekDate, "A");                
                calculate(currentDate, EndWeekDate, "B");
                calculate(currentDate, EndWeekDate, "C");
                calculate(currentDate, EndWeekDate, "D");
                calculate(currentDate, EndWeekDate, "F");

				textbox.Text += Environment.NewLine;
				
                currentDate = currentDate.AddDays(7);
                EndWeekDate = currentDate.AddDays(6);
            }
        }

        void calculate(DateTime StartWeekDate, DateTime EndWeekDate, string name)
        {
            list = new List<TempClass>();
			
            j1 = datatable.AsEnumerable().Select(a => new TempClass
            {
                Date = a.Field<DateTime>("Date"),
                Employee = a.Field<string>("Employee"),
                Job1 = a.Field<double>("Job1"),
                Job2 = a.Field<double>("Job2")
            }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job1);

            j2 = datatable.AsEnumerable().Select(a => new TempClass
            {
                Date = a.Field<DateTime>("Date"),
                Employee = a.Field<string>("Employee"),
                Job1 = a.Field<double>("Job1"),
                Job2 = a.Field<double>("Job2")
            }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job2);

            GroupByWeek.Rows.Add(new Object[] { name, StartWeekDate.ToShortDateString(), EndWeekDate.ToShortDateString(), j1, j2 });
            textbox.Text += name + "   " + StartWeekDate.ToShortDateString() + "   " + EndWeekDate.ToShortDateString() + "   " + j1 + "   " + j2 + Environment.NewLine;
        }
    }

    public class TempClass
    {
        public DateTime Date
        {
            get;
            set;
        }

        public string Employee
        {
            get;
            set;
        }
        public double Job1
        {
            get;
            set;
        }
        public double Job2
        {
            get;
            set;
        }
    }
}

[Radha Shyam]

Anybody looking for the solution here it is:

http://stackoverflow.com/questions/15898962/put-value-in-datatable-group-by-week/15904441#15904441