none
data table calculation?

    Question

  • Hi All,

    I have a below data table,

    class Program
        {
            static void Main(string[] args)
            {
                // Get the DataTable.
                DataTable table = GetTable();
            }
    
            static DataTable GetTable()
            {
                DataTable table = new DataTable();
                table.Columns.Add("GRP_ID", typeof(int));
                table.Columns.Add("SEQ", typeof(string));
                table.Columns.Add("NAME", typeof(string));
                table.Columns.Add("PROJ_ID", typeof(int));
                table.Columns.Add("A_COST", typeof(decimal));
                table.Columns.Add("A_Actual", typeof(decimal));
                table.Columns.Add("B_COST", typeof(decimal));
                table.Columns.Add("B_Actual", typeof(decimal));
    
                table.Rows.Add(1, "1.0", "A1", 9001, 20, 30, 40, 10);
                table.Rows.Add(1, "1.0", "A1", 9002, 40, 20, 50, 10);
                table.Rows.Add(2, "4.0", "B1", 9003, 50, 20, 70, 50);
                return table;
            }
        }

    I need to show output like below, sum of cost base on GRP_ID and then Grand Total within C# code, please help...

    1. TOTAL_COST = A_COST + B_COST

    2. TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL

    3. % DIFF = ((TOTAL_COST - TOTAL ACTUAL)/TOTAL_COST) * 100

    Friday, December 28, 2012 5:33 PM

Answers

  • Yazdani : This is not 100% solution but does this help ?

             
                var A_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("A_COST"));
                var B_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("B_COST"));
                var TOTAL_COST = A_COST + B_COST;

                var A_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("A_ACTUAL"));
                var B_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("B_ACTUAL"));
                var TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL;
                var diff = ((TOTAL_COST - TOTAL_ACTUAL) / TOTAL_COST) * 100;

                Console.WriteLine(TOTAL_COST);

    Also i did start this but could not complete using linq.. may be this is a partial solution which you can modify as needed to your needs

    public static void Main(string[] args)
            {
                DataTable table = GetTable();
     
            
                var query = from x in table.AsEnumerable()   
                            group x  by new 
                                { 
                                  PROJ_ID = x["PROJ_ID"],
                                  TOTAL_COST =  Convert.ToDecimal( x["A_COST"]) + Convert.ToDecimal(x["B_COST"]),
                                  TOTAL_ACTUAL = Convert.ToDecimal(x["A_ACTUAL"]) + Convert.ToDecimal(x["B_ACTUAL"]),
                                  NAME = x["SEQ"] + "_" + x["NAME"]
                                } 
                                into G
                                select new 
                                 {
                                    PROJ_ID= G.Key.PROJ_ID,
                                    NAME=   G.Key.NAME,
                                    TOTAL_COST=  G.Key.TOTAL_COST,
                                    TOTAL_ACTUAL =    G.Key.TOTAL_ACTUAL,
                                    DIFF = G.Key.TOTAL_ACTUAL/G.Key.TOTAL_COST * 100
                                 };
    
                DataTable newTable = GetNewTable();
                foreach (var row in query)
                {
                    newTable.Rows.Add(row.PROJ_ID, row.NAME, row.TOTAL_COST, row.TOTAL_ACTUAL, row.DIFF);
                }
                List<string> namesList = newTable.AsEnumerable().Select(x => x.Field<string>("NAME")).Distinct().ToList();
    
                if(newTable.Rows.Count > 0)
                {
                    var sumTotal_Cost = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_COST"));
                    var sumTotal_Actual = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
                    DataRow totalRow = newTable.Rows.Add(0,"GRAND TOTAL",sumTotal_Cost,sumTotal_Actual,sumTotal_Actual/sumTotal_Cost * 100 );
                    newTable.AcceptChanges();
                }
                foreach (var name in namesList)
                {
                    DataRow[] objRows = (from row in newTable.AsEnumerable()
                                        where row["NAME"].Equals(name)
                                        select row).ToArray();
                     if (objRows.Length > 0)
                    {
                        var subTOTAL_COST = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_COST"));
                        var subTOTAL_ACTUAL = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
                        DataRow subTotalRows = newTable.Rows.Add(objRows[0]["PROJ_ID"], name + " TOTAL", subTOTAL_COST, subTOTAL_ACTUAL,  subTOTAL_ACTUAL/subTOTAL_COST * 100 );
                        newTable.AcceptChanges();
                     }
                }
                newTable.DefaultView.Sort = "NAME" + " " + "ASC";
                newTable = newTable.DefaultView.ToTable();
                foreach (DataRow row in newTable.Rows)
                {
                    Console.WriteLine("{0} {1} {2} {3} {4}", row["NAME"], row["PROJ_ID"].ToString(),row["TOTAL_COST"], row["TOTAL_ACTUAL"], row["DIFF"]);
                }
    
           }
             static DataTable GetNewTable()
             {
                 DataTable table = new DataTable();
                 table.Columns.Add("PROJ_ID", typeof(int));
                 table.Columns.Add("NAME", typeof(string));
                 table.Columns.Add("TOTAL_COST", typeof(decimal));
                 table.Columns.Add("TOTAL_ACTUAL", typeof(decimal));
                 table.Columns.Add("DIFF", typeof(decimal));           
                 return table;
             }


    • Edited by Venkat786 Sunday, December 30, 2012 9:39 PM
    • Marked as answer by Yazdani ISTS Monday, December 31, 2012 3:46 AM
    Saturday, December 29, 2012 11:34 PM

All replies

  • Add new column "TOTAL_COST"

    Set table.Columns["TOTAL_COST "].Expression property with required expression.


    Ashutosh Tripathi

    Saturday, December 29, 2012 8:14 AM
  • thanx for reply.

    could you please focus more?

    mainly how to loop under loop...i think i need to do multi level looping...help me here

    i don't want full 100% solution, just guidline with some line of code.

    Saturday, December 29, 2012 9:50 AM
  • Yazdani : This is not 100% solution but does this help ?

             
                var A_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("A_COST"));
                var B_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("B_COST"));
                var TOTAL_COST = A_COST + B_COST;

                var A_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("A_ACTUAL"));
                var B_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("B_ACTUAL"));
                var TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL;
                var diff = ((TOTAL_COST - TOTAL_ACTUAL) / TOTAL_COST) * 100;

                Console.WriteLine(TOTAL_COST);

    Also i did start this but could not complete using linq.. may be this is a partial solution which you can modify as needed to your needs

    public static void Main(string[] args)
            {
                DataTable table = GetTable();
     
            
                var query = from x in table.AsEnumerable()   
                            group x  by new 
                                { 
                                  PROJ_ID = x["PROJ_ID"],
                                  TOTAL_COST =  Convert.ToDecimal( x["A_COST"]) + Convert.ToDecimal(x["B_COST"]),
                                  TOTAL_ACTUAL = Convert.ToDecimal(x["A_ACTUAL"]) + Convert.ToDecimal(x["B_ACTUAL"]),
                                  NAME = x["SEQ"] + "_" + x["NAME"]
                                } 
                                into G
                                select new 
                                 {
                                    PROJ_ID= G.Key.PROJ_ID,
                                    NAME=   G.Key.NAME,
                                    TOTAL_COST=  G.Key.TOTAL_COST,
                                    TOTAL_ACTUAL =    G.Key.TOTAL_ACTUAL,
                                    DIFF = G.Key.TOTAL_ACTUAL/G.Key.TOTAL_COST * 100
                                 };
    
                DataTable newTable = GetNewTable();
                foreach (var row in query)
                {
                    newTable.Rows.Add(row.PROJ_ID, row.NAME, row.TOTAL_COST, row.TOTAL_ACTUAL, row.DIFF);
                }
                List<string> namesList = newTable.AsEnumerable().Select(x => x.Field<string>("NAME")).Distinct().ToList();
    
                if(newTable.Rows.Count > 0)
                {
                    var sumTotal_Cost = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_COST"));
                    var sumTotal_Actual = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
                    DataRow totalRow = newTable.Rows.Add(0,"GRAND TOTAL",sumTotal_Cost,sumTotal_Actual,sumTotal_Actual/sumTotal_Cost * 100 );
                    newTable.AcceptChanges();
                }
                foreach (var name in namesList)
                {
                    DataRow[] objRows = (from row in newTable.AsEnumerable()
                                        where row["NAME"].Equals(name)
                                        select row).ToArray();
                     if (objRows.Length > 0)
                    {
                        var subTOTAL_COST = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_COST"));
                        var subTOTAL_ACTUAL = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
                        DataRow subTotalRows = newTable.Rows.Add(objRows[0]["PROJ_ID"], name + " TOTAL", subTOTAL_COST, subTOTAL_ACTUAL,  subTOTAL_ACTUAL/subTOTAL_COST * 100 );
                        newTable.AcceptChanges();
                     }
                }
                newTable.DefaultView.Sort = "NAME" + " " + "ASC";
                newTable = newTable.DefaultView.ToTable();
                foreach (DataRow row in newTable.Rows)
                {
                    Console.WriteLine("{0} {1} {2} {3} {4}", row["NAME"], row["PROJ_ID"].ToString(),row["TOTAL_COST"], row["TOTAL_ACTUAL"], row["DIFF"]);
                }
    
           }
             static DataTable GetNewTable()
             {
                 DataTable table = new DataTable();
                 table.Columns.Add("PROJ_ID", typeof(int));
                 table.Columns.Add("NAME", typeof(string));
                 table.Columns.Add("TOTAL_COST", typeof(decimal));
                 table.Columns.Add("TOTAL_ACTUAL", typeof(decimal));
                 table.Columns.Add("DIFF", typeof(decimal));           
                 return table;
             }


    • Edited by Venkat786 Sunday, December 30, 2012 9:39 PM
    • Marked as answer by Yazdani ISTS Monday, December 31, 2012 3:46 AM
    Saturday, December 29, 2012 11:34 PM
  • Thank You Very Much for ur detail and nice reply and ur effort...help me a lot...i will able to complete it. Thanks
    Monday, December 31, 2012 3:47 AM