Click here to Skip to main content
15,922,427 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem, i need help
I have table

ID EmpID Month_salary DaTe WorkShifts
1 001 MONTH01 2012-01-01 1
2 001 MONTH01 2012-01-02 2
3 001 MONTH01 2012-01-03 3
4 001 MONTH01 2012-01-03 1
5 001 MONTH01 2012-01-04 2
6 001 MONTH01 2012-01-05 3
.. ... ........ .......... ..
31 001 MONTH01 2012-01-31 3

I want datagridview :



EmpID2012-01-01
1 |2 |3
2012-01-02
1 |2 |3
2012-01-03
1 |2 |3
..........2012-01-31
1 |2 |3
001x|x|xx|x|xx|x|x..........x|x|x
002x|x|xx|x|xx|x|x..........x|x|x



1,2,3 means the work shift, each day there are three shifts. For example, here a month has 31 days. letter x means that the employee has to work.
How can I do to do as requested? Thanks all
Posted
Updated 3-Jun-12 18:02pm
v2
Comments
VJ Reddy 4-Jun-12 4:04am    
Thank you for accepting the solution :)

The given data can be transformed into the required format using LINQ query as shown below:

C#
DataTable shiftData = new DataTable("ShiftData");
shiftData.Columns.Add("Id",typeof(int),null);
shiftData.Columns.Add("EmpId",typeof(string),null);
shiftData.Columns.Add("Month",typeof(string),null);
shiftData.Columns.Add("Date",typeof(DateTime),null);
shiftData.Columns.Add("Shift",typeof(int),null);

shiftData.Rows.Add(1,"001","Month01",new DateTime(2012,1,1),1);
shiftData.Rows.Add(2,"001","Month01",new DateTime(2012,1,2),2);
shiftData.Rows.Add(3,"001","Month01",new DateTime(2012,1,3),3);
shiftData.Rows.Add(4,"001","Month01",new DateTime(2012,1,3),1);
shiftData.Rows.Add(5,"001","Month01",new DateTime(2012,1,4),2);
shiftData.Rows.Add(6,"001","Month01",new DateTime(2012,1,5),3);
shiftData.Rows.Add(7,"002","Month01",new DateTime(2012,1,1),1);
shiftData.Rows.Add(8,"002","Month01",new DateTime(2012,1,2),2);
shiftData.Rows.Add(9,"002","Month01",new DateTime(2012,1,3),3);
shiftData.Rows.Add(10,"001","Month02",new DateTime(2012,2,3),1);
shiftData.Rows.Add(11,"001","Month02",new DateTime(2012,2,4),2);
shiftData.Rows.Add(12,"001","Month02",new DateTime(2012,2,5),3);

DataTable empShifts = new DataTable("EmpShifts");
empShifts.Columns.Add("EmpId",typeof(string),null);
empShifts.Columns.Add("Month",typeof(string),null);

for(int i=1; i<= 31; i++)
    for(int j=1; j <=3; j++)
        empShifts.Columns.Add(i.ToString()+"-" + j.ToString(),typeof(string),null);

shiftData.AsEnumerable().GroupBy (dr => dr.Field<string>("EmpId")).Select (er => {
er.GroupBy (dr2 => dr2.Field<string>("Month")).Select (mr => {
    DataRow row = empShifts.NewRow();
    row["EmpId"]=er.Key;
    row["Month"]=mr.Key;
    mr.Select (ms => 
    	row[ms.Field<datetime>("Date").Day + "-" + ms.Field<int>("Shift").ToString()]="x").Count ();
    empShifts.Rows.Add(row);
    return mr;
    }).Count ();
return er;
}).Count ();

	
//ShiftData
//Id EmpId Month Date Shift
//1 001 Month01 1/1/2012 12:00:00 AM 1 
//2 001 Month01 1/2/2012 12:00:00 AM 2 
//3 001 Month01 1/3/2012 12:00:00 AM 3 
//4 001 Month01 1/3/2012 12:00:00 AM 1 
//5 001 Month01 1/4/2012 12:00:00 AM 2 
//6 001 Month01 1/5/2012 12:00:00 AM 3 
//7 002 Month01 1/1/2012 12:00:00 AM 1 
//8 002 Month01 1/2/2012 12:00:00 AM 2 
//9 002 Month01 1/3/2012 12:00:00 AM 3 
//10 001 Month02 2/3/2012 12:00:00 AM 1 
//11 001 Month02 2/4/2012 12:00:00 AM 2 
//12 001 Month02 2/5/2012 12:00:00 AM 3 

//empShift
//Headings--->EmpId Month 1-1 1-2 1-3 2-1 2-2 2-3 3-1 3-2 3-3 4-1 4-2 4-3 5-1 5-2 5-3 6-1 6-2 6-3 7-1 7-2 7-3 8-1 8-2 8-3 9-1 9-2 9-3 10-1 10-2 10-3 11-1 11-2 11-3 12-1 12-2 12-3 13-1 13-2 13-3 14-1 14-2 14-3 15-1 15-2 15-3 16-1 16-2 16-3 17-1 17-2 17-3 18-1 18-2 18-3 19-1 19-2 19-3 20-1 20-2 20-3 21-1 21-2 21-3 22-1 22-2 22-3 23-1 23-2 23-3 24-1 24-2 24-3 25-1 25-2 25-3 26-1 26-2 26-3 27-1 27-2 27-3 28-1 28-2 28-3 29-1 29-2 29-3 30-1 30-2 30-3 31-1 31-2 31-3 
//Row1--->001 Month01 x null  null  null  x null  x null  x null  x null  null  null  x null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  
//Row2--->001 Month02 null  null  null  null  null  null  x null  null  null  x null  null  null  x null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  
//Row3--->002 Month01 x null  null  null  x null  null  null  x null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  </int></datetime></string></string>

The LINQ has deferred execution model, which means the query will be executed only when it is iterated or a scalar method like Count is called on it. Hence, in the above code to force immediate execution of the query Count extension method is used.

The DataTable generated above can be bound to the DataGridView.

The DataGridView does not have an in built feature to display Header in two rows as required. However, as explained here http://www.murrayhilltech.com/articles/articles.aspx?key=8[^] the DataGridView headers can be custom drawn.
 
Share this answer
 
Comments
violetqa 4-Jun-12 2:08am    
Thanks you very much. I will try
violetqa 4-Jun-12 2:34am    
Thanks you. I did it. Hihi
VJ Reddy 4-Jun-12 3:46am    
You're welcome and thank you for the response :)
If the solution is helpful then the solution may be considered to vote and accept
Thank you.
Pivot your data on the date, it should be done in the database
 
Share this answer
 
Comments
violetqa 4-Jun-12 0:04am    
Can you guide details more? Thanks
violetqa 4-Jun-12 0:13am    
I were pivot your data on the date but how do I get for each day work shift?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900