473,507 Members | 3,706 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Linq - Group by week in datatable

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

Expand|Select|Wrap|Line Numbers
  1. DataTable datatable = new DataTable("Employee");
  2. datatable.Columns.Add("Date", typeof(string));
  3. datatable.Columns.Add("Employee", typeof(string));
  4. datatable.Columns.Add("Job1", typeof(double));
  5. datatable.Columns.Add("Job2", typeof(double));
  6.  
  7. datatable.Rows.Add(new Object[] { "1/4/2013", "A", 1.3, 2 });
  8. datatable.Rows.Add(new Object[] { "1/4/2013", "B", 2.5, 6 });
  9. datatable.Rows.Add(new Object[] { "1/6/2013", "C", 3.7, 2.4 });
  10. datatable.Rows.Add(new Object[] { "1/7/2013", "D", 11, 0.0 });
  11. datatable.Rows.Add(new Object[] { "1/7/2013", "F", 334, 0 });
  12. datatable.Rows.Add(new Object[] { "1/8/2013", "A", 1.87, 1 });
  13. datatable.Rows.Add(new Object[] { "1/8/2013", "B", 6.85, 2 });
  14. datatable.Rows.Add(new Object[] { "1/9/2013", "C", 58, 226 });
  15. datatable.Rows.Add(new Object[] { "1/16/2013", "A", 9.43, 1.45 });
  16. datatable.Rows.Add(new Object[] { "1/16/2013", "B", 5.27, 0.6 });
  17. datatable.Rows.Add(new Object[] { "1/22/2013", "C", 45.4, 5 });
  18. datatable.Rows.Add(new Object[] { "1/23/2013", "A", 44, 4.78 });
  19. datatable.Rows.Add(new Object[] { "1/29/2013", "B", 45, 40 });
  20. datatable.Rows.Add(new Object[] { "2/2/2013", "C", 45, 54.12 });
  21. datatable.Rows.Add(new Object[] { "2/2/2013", "D", 7, 4.4587 });
  22. datatable.Rows.Add(new Object[] { "2/3/2013", "F", "265", 11.486 });
  23. 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.
Apr 9 '13 #1
4 5805
vijay6
158 New Member
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)


Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Linq;
  5. using System.Windows.Forms;
  6.  
  7. namespace WindowsFormsApplication1
  8. {
  9.     public partial class Form1 : Form
  10.     {   
  11.         DataTable datatable;
  12.  
  13.         public Form1()
  14.         {
  15.             InitializeComponent();
  16.         }
  17.  
  18.         private void Form1_Load(object sender, EventArgs e)
  19.         {
  20.             DateTime StartDate = new DateTime(2012, 12, 31); // 12/31/2012
  21.             DateTime EndDate = new DateTime(2013, 3, 3); // 03/03/2013
  22.  
  23.             DateTime StartWeekDate = StartDate;
  24.             DateTime EndWeekDate = StartWeekDate.AddDays(6);
  25.  
  26.             datatable = new DataTable("EmployeeTable");
  27.             datatable.Columns.Add("Date", typeof(DateTime));
  28.             datatable.Columns.Add("Employee", typeof(string));
  29.             datatable.Columns.Add("Job1", typeof(double));
  30.             datatable.Columns.Add("Job2", typeof(double));
  31.  
  32.             datatable.Rows.Add(new Object[] { "1/4/2013", "A", 1.3, 2 });
  33.             datatable.Rows.Add(new Object[] { "1/4/2013", "B", 2.5, 6 });
  34.             datatable.Rows.Add(new Object[] { "1/6/2013", "C", 3.7, 2.4 });
  35.             datatable.Rows.Add(new Object[] { "1/7/2013", "D", 11, 0.0 });
  36.             datatable.Rows.Add(new Object[] { "1/7/2013", "F", 334, 0 });
  37.             datatable.Rows.Add(new Object[] { "1/8/2013", "A", 1.87, 1 });
  38.             datatable.Rows.Add(new Object[] { "1/8/2013", "B", 6.85, 2 });
  39.             datatable.Rows.Add(new Object[] { "1/9/2013", "C", 58, 226 });
  40.             datatable.Rows.Add(new Object[] { "1/16/2013", "A", 9.43, 1.45 });
  41.             datatable.Rows.Add(new Object[] { "1/16/2013", "B", 5.27, 0.6 });
  42.             datatable.Rows.Add(new Object[] { "1/22/2013", "C", 45.4, 5 });
  43.             datatable.Rows.Add(new Object[] { "1/23/2013", "A", 44, 4.78 });
  44.             datatable.Rows.Add(new Object[] { "1/29/2013", "B", 45, 40 });
  45.             datatable.Rows.Add(new Object[] { "2/2/2013", "C", 45, 54.12 });
  46.             datatable.Rows.Add(new Object[] { "2/2/2013", "D", 7, 4.4587 });
  47.             datatable.Rows.Add(new Object[] { "2/3/2013", "F", 265, 11.486 });
  48.             datatable.Rows.Add(new Object[] { "3/3/2013", "A", 25, 28.124 });
  49.  
  50.             for (DateTime currentDate = StartWeekDate; currentDate <= EndDate; )
  51.             {
  52.                 calculate(currentDate, EndWeekDate, "A");
  53.                 calculate(currentDate, EndWeekDate, "B");
  54.                 calculate(currentDate, EndWeekDate, "C");
  55.                 calculate(currentDate, EndWeekDate, "D");
  56.                 calculate(currentDate, EndWeekDate, "F");
  57.  
  58.                 currentDate = currentDate.AddDays(7);
  59.                 EndWeekDate = currentDate.AddDays(6);
  60.             }
  61.         }
  62.  
  63.         private void calculate(DateTime StartWeekDate, DateTime EndWeekDate, string name)
  64.         {
  65.             List<TempClass> list = new List<TempClass>();
  66.  
  67.             Console.WriteLine(name + "     " + StartWeekDate.ToShortDateString() + "     " + EndWeekDate.ToShortDateString() + "     ");
  68.  
  69.             Console.WriteLine(datatable.AsEnumerable().Select(a => new TempClass
  70.             {
  71.                 Date = a.Field<DateTime>("Date"),
  72.                 Employee = a.Field<string>("Employee"),
  73.                 Job1 = a.Field<double>("Job1"),
  74.                 Job2 = a.Field<double>("Job2")
  75.             }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job1).ToString());
  76.  
  77.             Console.WriteLine(datatable.AsEnumerable().Select(a => new TempClass
  78.             {
  79.                 Date = a.Field<DateTime>("Date"),
  80.                 Employee = a.Field<string>("Employee"),
  81.                 Job1 = a.Field<double>("Job1"),
  82.                 Job2 = a.Field<double>("Job2")
  83.             }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job2).ToString());
  84.         }
  85.     }
  86.  
  87.     public class TempClass
  88.     {
  89.         public DateTime Date
  90.         {
  91.             get;
  92.             set;
  93.         }
  94.  
  95.         public string Employee
  96.         {
  97.             get;
  98.             set;
  99.         }
  100.         public double Job1
  101.         {
  102.             get;
  103.             set;
  104.         }
  105.         public double Job2
  106.         {
  107.             get;
  108.             set;
  109.         }
  110.     }
  111. }
Apr 11 '13 #2
Radha Shyam
9 New Member
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!
Apr 11 '13 #3
vijay6
158 New Member
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.


Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Drawing;
  5. using System.Linq;
  6. using System.Windows.Forms;
  7.  
  8. namespace WindowsFormsApplication1
  9. {
  10.     public partial class Form1 : Form
  11.     {
  12.         DataTable datatable;
  13.         TextBox textbox;
  14.         DataTable GroupByWeek;
  15.         List<TempClass> list;
  16.         double j1, j2;
  17.  
  18.         public Form1()
  19.         {
  20.             InitializeComponent();
  21.         }
  22.  
  23.         private void Form1_Load(object sender, EventArgs e)
  24.         {
  25.             DateTime StartDate = new DateTime(2012, 12, 31);
  26.             DateTime EndDate = new DateTime(2013, 3, 3);
  27.  
  28.             DateTime StartWeekDate = StartDate;
  29.             DateTime EndWeekDate = StartWeekDate.AddDays(6);
  30.  
  31.             datatable = new DataTable("EmployeeTable");
  32.             datatable.Columns.Add("Date", typeof(DateTime));
  33.             datatable.Columns.Add("Employee", typeof(string));
  34.             datatable.Columns.Add("Job1", typeof(double));
  35.             datatable.Columns.Add("Job2", typeof(double));
  36.  
  37.             datatable.Rows.Add(new Object[] { "1/4/2013", "A", 1.3, 2 });
  38.             datatable.Rows.Add(new Object[] { "1/4/2013", "B", 2.5, 6 });
  39.             datatable.Rows.Add(new Object[] { "1/6/2013", "C", 3.7, 2.4 });
  40.             datatable.Rows.Add(new Object[] { "1/7/2013", "D", 11, 0.0 });
  41.             datatable.Rows.Add(new Object[] { "1/7/2013", "F", 334, 0 });
  42.             datatable.Rows.Add(new Object[] { "1/8/2013", "A", 1.87, 1 });
  43.             datatable.Rows.Add(new Object[] { "1/8/2013", "B", 6.85, 2 });
  44.             datatable.Rows.Add(new Object[] { "1/9/2013", "C", 58, 226 });
  45.             datatable.Rows.Add(new Object[] { "1/16/2013", "A", 9.43, 1.45 });
  46.             datatable.Rows.Add(new Object[] { "1/16/2013", "B", 5.27, 0.6 });
  47.             datatable.Rows.Add(new Object[] { "1/22/2013", "C", 45.4, 5 });
  48.             datatable.Rows.Add(new Object[] { "1/23/2013", "A", 44, 4.78 });
  49.             datatable.Rows.Add(new Object[] { "1/29/2013", "B", 45, 40 });
  50.             datatable.Rows.Add(new Object[] { "2/2/2013", "C", 45, 54.12 });
  51.             datatable.Rows.Add(new Object[] { "2/2/2013", "D", 7, 4.4587 });
  52.             datatable.Rows.Add(new Object[] { "2/3/2013", "F", 265, 11.486 });
  53.             datatable.Rows.Add(new Object[] { "3/3/2013", "A", 25, 28.124 });
  54.  
  55.             textbox = new TextBox();
  56.             textbox.Width = 250;
  57.             textbox.Height = 200;
  58.             textbox.Multiline = true;
  59.             textbox.ScrollBars = ScrollBars.Vertical;
  60.             textbox.Location = new Point(12, 12);
  61.             this.Controls.Add(textbox);
  62.  
  63.             textbox.Text = String.Empty;
  64.  
  65.             GroupByWeek = new DataTable("GroupByWeek");
  66.             GroupByWeek.Columns.Add("Employee", typeof(string));
  67.             GroupByWeek.Columns.Add("From", typeof(DateTime));
  68.             GroupByWeek.Columns.Add("To", typeof(DateTime));
  69.             GroupByWeek.Columns.Add("Job1", typeof(double));
  70.             GroupByWeek.Columns.Add("Job2", typeof(double));
  71.  
  72.             for (DateTime currentDate = StartWeekDate; currentDate <= EndDate; )
  73.             {
  74.                 calculate(currentDate, EndWeekDate, "A");                
  75.                 calculate(currentDate, EndWeekDate, "B");
  76.                 calculate(currentDate, EndWeekDate, "C");
  77.                 calculate(currentDate, EndWeekDate, "D");
  78.                 calculate(currentDate, EndWeekDate, "F");
  79.  
  80.                 textbox.Text += Environment.NewLine;
  81.  
  82.                 currentDate = currentDate.AddDays(7);
  83.                 EndWeekDate = currentDate.AddDays(6);
  84.             }
  85.         }
  86.  
  87.         void calculate(DateTime StartWeekDate, DateTime EndWeekDate, string name)
  88.         {
  89.             list = new List<TempClass>();
  90.  
  91.             j1 = datatable.AsEnumerable().Select(a => new TempClass
  92.             {
  93.                 Date = a.Field<DateTime>("Date"),
  94.                 Employee = a.Field<string>("Employee"),
  95.                 Job1 = a.Field<double>("Job1"),
  96.                 Job2 = a.Field<double>("Job2")
  97.             }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job1);
  98.  
  99.             j2 = datatable.AsEnumerable().Select(a => new TempClass
  100.             {
  101.                 Date = a.Field<DateTime>("Date"),
  102.                 Employee = a.Field<string>("Employee"),
  103.                 Job1 = a.Field<double>("Job1"),
  104.                 Job2 = a.Field<double>("Job2")
  105.             }).Where(b => b.Date >= StartWeekDate && b.Date <= EndWeekDate).Where(c => c.Employee == name).Sum(c => c.Job2);
  106.  
  107.             GroupByWeek.Rows.Add(new Object[] { name, StartWeekDate.ToShortDateString(), EndWeekDate.ToShortDateString(), j1, j2 });
  108.             textbox.Text += name + "   " + StartWeekDate.ToShortDateString() + "   " + EndWeekDate.ToShortDateString() + "   " + j1 + "   " + j2 + Environment.NewLine;
  109.         }
  110.     }
  111.  
  112.     public class TempClass
  113.     {
  114.         public DateTime Date
  115.         {
  116.             get;
  117.             set;
  118.         }
  119.  
  120.         public string Employee
  121.         {
  122.             get;
  123.             set;
  124.         }
  125.         public double Job1
  126.         {
  127.             get;
  128.             set;
  129.         }
  130.         public double Job2
  131.         {
  132.             get;
  133.             set;
  134.         }
  135.     }
  136. }
Apr 12 '13 #4
Radha Shyam
9 New Member
Anybody looking for the solution here it is:
http://stackoverflow.com/questions/1...04441#15904441
Jun 4 '13 #5

Sign in to post your reply or Sign up for a free account.

Similar topics
1
1387
Aggregate functions with GROUP BY Clauses on DataTable?
by: Job Lot | last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values: Date Amount Int Balance 1/1/2004 5000.00 50.00 5050.00...
.NET Framework
5
26602
DataTable.Select distinct Method Query
by: =?Utf-8?B?dHZpbg==?= | last post by:
Hi everybody i have a datatable, i want to select distinct value from datatable and not database. so is there any method that can provide this query: Select DISTINCT from DataTable please help...
.NET Framework
2
1556
How to delete a group of rows from a DatTable
by: JoaquimC | last post by:
Hello, I need to delete a group of rows from a DataTable based on a condition. Something like: string CRefeicao = "01", CFamiliaPrato = "02"; foreach (DataRow LinhaDetalhe in Detalhe.Rows)...
.NET Framework
0
1350
Need Help On Best Querying ( is LINQ work with Huge amount of data..)
by: ranganadh | last post by:
Dear Group members, I am new to LINQ, pls help on the deeling with huge amount of data with the C# stand Alone application. I have two file, which contains more then 2 lacs lines in every...
C# / C Sharp
5
1267
Special LINQ group available?
by: Neil | last post by:
Hi group, is there a special C# LINQ group available? Regards
C# / C Sharp
0
1007
Re: Linq Group by whole day
by: Marc Gravell | last post by:
I hadn't yet needed to do this, but I'm kinda glad it works! Shame about all those DATEADDs though... I've just run a test, and a CAST(FLOOR(CAST(x as float)) as datetime) is twice as quick......
C# / C Sharp
0
1130
linq to DataTable issues
by: Andy B | last post by:
Hi... I have this DataTable: StockContract.Dictionary. I need a linq to DataTable query that will select all rows where the "Word" column values match the WordTextBox.Text value. I am running...
ASP.NET
9
11908
SQL to Linq - Left, Right and Inner Joins
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
C# / C Sharp
5
5487
linq group by month problem
by: Seb | last post by:
I want to count activity in a given month. I'm trying to do so with the linq code below however it reports: Error 1 'a' is inaccessible due to its protection level var ActivityByMonths = from a...
C# / C Sharp
4
1451
LINQ group
by: Peter Morris | last post by:
responseData.Signals is a List<SignalInfo> var signalGroups = from signal in responseData.Signals where signal.DisplayPath.StartsWith("AppMenu") orderby signal.DisplayPath group signal by...
C# / C Sharp
0
7221
marktang
What is ONU?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
General
0
7109
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
7313
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++
0
7481
tracyyun
Discussion: How does Zigbee compare with other wireless protocols in smart home applications?
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
General
0
5619
agi2029
AI Job Threat for Devs
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
Career Advice
1
5039
isladogs
Access Europe - Using VBA to create a class based on a table - Wed 1 May
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
Microsoft Access / VBA
0
3190
Trying to create a lan-to-lan vpn between two differents networks
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
Networking - Hardware / Configuration
0
1537
transfer the data from one system to another through ip address
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
C# / C Sharp
0
411
bsmnconsultancy
Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
General

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes
How to Post and Respond on Bytes
How to Promote and Link on Bytes
How to increase your Ranking on Bytes
Become a Recognized Expert on Bytes
Feedback Welcomed! Contact Us