locked
How to create Sub Totals on a Weekly basis for the month RRS feed

  • 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 92

    So 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 74

    Subtotal 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 97

    subtotal ...

    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

    subtotal ...

    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) / @NumberExaminers

    FROM 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_DATE

    Should 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