Making summary (summation table) from two data tables

[Mohammad Nadeem Alam]

Hi Everyone,

I need a help. I have to develop a console application which takes input of two csv files (or excel files). I am importing these files and assigning into Data Table. one csv file has employee details with project id and other csv file has payroll items and respective amount with employee id and name as below. Please advice to calculate data summary table. Below example shows the details.

I have a two data table and need to make data summary, table, it may be a data table or array. But it should be as below mentioned data summary.

Employee Details


Employee ID



Emplyee name


Project / Department Code

Allocation​

101140


Name1


151

100​

101141


Name2


152

80​

101142


Name3


153

90​

101144


Name4


153

10​

101145


Name5


152

40​

100543


Name6


151

30​

Employee Payroll

Employee Code​

Gross Pay​

Total Taxable Pay​

Total Non-taxable Pay​

Total ER NICable Pay​

Total Deductions​

Employee NI Amount​

Employer NI Amount​

Employee Tax Amount​

Total AEO Deducted​

Student Loans Deducted​

101140

1000.00​

2000.00​

5000.00​

00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


101142

2000.00​

3000.00​

3000.00​

00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


101145

6000.00​

1000.00​

2000.00​

00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


101141

9000.00​

7000.00​

6000.00​

00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


101142

1000.00​

6000.00​

2000.00​

00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


100544

2000.00​

6,622.80​

9000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00


00000.00



Data Summary

Project / Department Code​

Gross Pay​

Total Taxable Pay​

Total Non-taxable Pay​

Total ER NICable Pay​

Total Deductions​

Employee NI Amount​

Employer NI Amount​

Employee Tax Amount​

Total AEO Deducted​

Student Loans Deducted​

151

1600​

152

9600​

153

2000​

Calculation: Gross Pay

151= (1000.00*1)+( 2000.00*.3)=1000.00+600=1600

152= (9000.00*.8)+6000.00*.4=7200+2400=9600

153=2000*.9+2000*.1=1800+200=2000

same way need to calculate for each payroll item

I have wrote below code, but stuck to apply logic for data summary table

//Load data

DataTable emplyeetable= new DataTable();
emplyeetable= ConvertCSVtoDataTable("C:\\abcd\\input\\workday.csv");

DataTable payroll = new DataTable();
payroll = ConvertCSVtoDataTable("C:\\abcd\\input\\payroll.csv");
int payrollcount = payroll.Columns.Count;

DataView view = new DataView(emplyeetable);
DataTable distinctValues = view.ToTable(true, "Project");

//DataTable table = GetTable();

//Make a summary table

DataTable DataSummary = new DataTable();
for (int i = 0; i < payrollcount; i++)
{

}

for (int i = 0; i < distinctValues.Rows.Count; i++)
{
DataRow oRow = distinctValues.DefaultView.Row;

}
Mohammad Nadeem Alam SME in Emirates NBD

Continue reading...