How to create Sub Totals on a Weekly basis for the month
Question
Answers
-
User-1174608757 posted
Hi coreysan,
According to your description, I have made a sample here. Since you have got the output from the database. I have set my database table as below:
Now firstly, since you would like to get the data of each column,I suggest you could use DataTable to save the table in datatbase,then you could use DataRow to get the data in each row.
In you case you could use foreach to get the result.We could define a variable ,for example 'idsum' to save the data of id.When the date is not Friday ,we add the id to idsum When it is Friday, we could add Friday data to the sumdata then show the data and clear the data of last week. Lastly, we could loop again.Here is the demo, I hope it could help you.
aspx.cs:
public partial class datatable : System.Web.UI.Page { private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { string sql = "select * from ss"; DataTable dt = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr)) { adapter.Fill(dt);// use datatable to save the database table } int a1sum = 0; int a2sum = 0; int a3sum = 0; foreach ( DataRow row in dt.Rows)// loop the data of row { DateTime time =DateTime.Parse( row["Date"].ToString());// get the time in the row int a1 = Convert.ToInt32(row["ID"]); int a2 = Convert.ToInt32(row["DBName"]); int a3 = Convert.ToInt32(row["Orgname"]); int index = row["Date"].ToString().LastIndexOf(" "); Response.Write(row["Date"].ToString().Substring(0,index) + " " + a1 + " " + a2 + " " + a3+"</br>");// show the data of each row if (time.DayOfWeek == DayOfWeek.Friday)// when it is Friday { a1sum += a1; a2sum += a2; a3sum += a3;// add Friday information Response.Write("Subtoatl:" + a1sum + " " + a2sum + " " + a3sum + "</br>");//show subtotal a1sum = 0; a2sum = 0; a3sum = 0;// clear this week information } else { a1sum += a1; a2sum += a2; a3sum += a3;// not Friday we just add value } } } }it shows as below:
Best Regards
Wei Zhang
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
All replies
-
User-1174608757 posted
Hi coreysan,
According to your description, I have made a sample here. Since you have got the output from the database. I have set my database table as below:
Now firstly, since you would like to get the data of each column,I suggest you could use DataTable to save the table in datatbase,then you could use DataRow to get the data in each row.
In you case you could use foreach to get the result.We could define a variable ,for example 'idsum' to save the data of id.When the date is not Friday ,we add the id to idsum When it is Friday, we could add Friday data to the sumdata then show the data and clear the data of last week. Lastly, we could loop again.Here is the demo, I hope it could help you.
aspx.cs:
public partial class datatable : System.Web.UI.Page { private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { string sql = "select * from ss"; DataTable dt = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr)) { adapter.Fill(dt);// use datatable to save the database table } int a1sum = 0; int a2sum = 0; int a3sum = 0; foreach ( DataRow row in dt.Rows)// loop the data of row { DateTime time =DateTime.Parse( row["Date"].ToString());// get the time in the row int a1 = Convert.ToInt32(row["ID"]); int a2 = Convert.ToInt32(row["DBName"]); int a3 = Convert.ToInt32(row["Orgname"]); int index = row["Date"].ToString().LastIndexOf(" "); Response.Write(row["Date"].ToString().Substring(0,index) + " " + a1 + " " + a2 + " " + a3+"</br>");// show the data of each row if (time.DayOfWeek == DayOfWeek.Friday)// when it is Friday { a1sum += a1; a2sum += a2; a3sum += a3;// add Friday information Response.Write("Subtoatl:" + a1sum + " " + a2sum + " " + a3sum + "</br>");//show subtotal a1sum = 0; a2sum = 0; a3sum = 0;// clear this week information } else { a1sum += a1; a2sum += a2; a3sum += a3;// not Friday we just add value } } } }it shows as below:
Best Regards
Wei Zhang
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
