Answered by:
How to create Sub Totals on a Weekly basis for the month

Question
-
User248267340 posted
In my output, I've listed data for monday-friday for the month, but would like to see if I can get weekly subtotals to display as well.
So far here's my output:
2019-02-01 00:00:00.000 113 28 226 76 0 443 88
2019-02-04 00:00:00.000 314 70 574 160 0 1118 223
2019-02-05 00:00:00.000 190 58 399 100 0 747 149
2019-02-06 00:00:00.000 365 60 900 106 0 1431 286
2019-02-07 00:00:00.000 206 41 307 108 0 662 132
2019-02-08 00:00:00.000 94 41 216 23 0 374 74
2019-02-11 00:00:00.000 500 83 892 174 0 1649 329
2019-02-12 00:00:00.000 210 37 346 64 2 659 131
2019-02-13 00:00:00.000 231 34 433 93 1 792 158
2019-02-14 00:00:00.000 163 15 334 62 0 574 114
2019-02-15 00:00:00.000 91 35 279 82 0 487 97
2019-02-18 00:00:00.000 474 100 961 120 0 1655 331
2019-02-19 00:00:00.000 183 22 281 64 0 550 110
2019-02-20 00:00:00.000 163 29 429 105 0 726 145
2019-02-21 00:00:00.000 294 68 433 117 1 913 182
2019-02-22 00:00:00.000 105 22 129 33 0 289 57
2019-02-25 00:00:00.000 207 16 205 35 1 464 92So it would be great if it looked like this:
2019-02-01 00:00:00.000 113 28 226 76 0 443 88
Subtotal: 113 28 226 76 0 443 88
2019-02-04 00:00:00.000 314 70 574 160 0 1118 223
2019-02-05 00:00:00.000 190 58 399 100 0 747 149
2019-02-06 00:00:00.000 365 60 900 106 0 1431 286
2019-02-07 00:00:00.000 206 41 307 108 0 662 132
2019-02-08 00:00:00.000 94 41 216 23 0 374 74Subtotal 1169..... etc. etc.
2019-02-11 00:00:00.000 500 83 892 174 0 1649 329
2019-02-12 00:00:00.000 210 37 346 64 2 659 131
2019-02-13 00:00:00.000 231 34 433 93 1 792 158
2019-02-14 00:00:00.000 163 15 334 62 0 574 114
2019-02-15 00:00:00.000 91 35 279 82 0 487 97subtotal ...
2019-02-18 00:00:00.000 474 100 961 120 0 1655 331
2019-02-19 00:00:00.000 183 22 281 64 0 550 110
2019-02-20 00:00:00.000 163 29 429 105 0 726 145
2019-02-21 00:00:00.000 294 68 433 117 1 913 182
2019-02-22 00:00:00.000 105 22 129 33 0 289 57subtotal ...
2019-02-25 00:00:00.000 207 16 205 35 1 464 92
subtotal ...
Any thoughts? I'm sure this has been done somewhere in the world! :)
Thank you very much for any tips or guidance!
Monday, February 25, 2019 11:34 PM
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
Wednesday, February 27, 2019 6:14 AM
All replies
-
User-1174608757 posted
Hi coreysan,
According to your description, could you please tell me how you get the output and what is the type of data ?Are all these datas string type and you put them in a string list ?
If you could post more details ,it will help me make a solution. I will try my best to solve it as soon as possible.
Best Regards
Wei Zhang
Tuesday, February 26, 2019 2:08 AM -
User248267340 posted
Wei,
Thanks very much for your willingness to help. I appreciate it!
Right now I have a stored proc that populates a table:
CREATE TABLE DailyData(ID varchar(20) NULL, DBName varchar(10) NULL, OrgName varchar(100) NULL, CPN_DATE datetime NULL, CPN_Notes varchar(1000) NULL)
And the select statement for output is this:
SELECT
DISTINCT cpn_date,
DB1=(SELECT COUNT(DBName) FROM DailyData WHERE DBName = 'DB1' and CPN_DATE = a.CPN_DATE),
DB2=(SELECT COUNT(DBName) FROM DailyData WHERE DBName = 'DB1' and CPN_DATE = a.CPN_DATE),
DB3=(SELECT COUNT(DBName) FROM DailyData WHERE DBName = 'DB3' and CPN_DATE = a.CPN_DATE),
DB4=(SELECT COUNT(DBName) FROM DailyData WHERE DBName = 'DB4' and CPN_DATE = a.CPN_DATE),
DB5=(SELECT COUNT(DBName) FROM DailyData WHERE DBName = 'DB5' and CPN_DATE = a.CPN_DATE),
TotClaims=(SELECT COUNT(DBName) FROM DailyData WHERE CPN_DATE = a.CPN_DATE),
Average=(SELECT COUNT(DBName) FROM DailyData WHERE CPN_DATE = a.CPN_DATE) / @NumberExaminersFROM DailyData a
WHERE CPN_DATE BETWEEN @StartDate and @EndDate and
DATEPART(dw, CPN_DATE) BETWEEN 2 and 6
GROUP BY CPN_DATE
ORDER BY CPN_DATEShould I try to include records with a Saturday date and group/sort on that?
Tuesday, February 26, 2019 7:00 PM -
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
Wednesday, February 27, 2019 6:14 AM -
User248267340 posted
Wei,
You went way beyond the call of duty! I clearly understand your solution, and that's what I will go with.
Very well done - thanks so much!
Corey
Wednesday, February 27, 2019 6:37 PM