Post
Ask Question

Month Wise Yearly Data With SQL Server

Suppose any One wants January to December months till staring year suppose 2010 all record to compare that in which year he got much profit he can calculate easily with the help of sql query, 
  1. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true" onrowdatabound="GridView1_RowDataBound" onrowcreated="GridView1_RowCreated" CellPadding="3" CellSpacing="3" Width="250px">  
  2.     <Columns>  
  3.         <asp:BoundField DataField="Month" HeaderText="Month" />  
  4.         <asp:BoundField DataField="Year" HeaderText="Year" />  
  5.         <asp:TemplateField HeaderText="TotalAmount" ItemStyle-HorizontalAlign="Right">  
  6.             <ItemTemplate>  
  7.                 <asp:Label ID="lblqty" runat="server" Text='<%# Eval("TotalTolling") %>' /> </ItemTemplate>  
  8.             <FooterTemplate>  
  9.                 <div style="text-align: right;">  
  10.                     <asp:Label ID="lblTotalqty" runat="server" /> </div>  
  11.             </FooterTemplate>  
  12.             <ItemStyle HorizontalAlign="Right"></ItemStyle>  
  13.         </asp:TemplateField>  
  14.     </Columns>  
  15.     <HeaderStyle BackColor="#990000" ForeColor="#FFFFCC" /> </asp:GridView>   
 /////////////// SQL QUERY 
  1. SELECT YEAR(Datecolumn) AS [Year],MONTH(Datecolumn) AS [Month],sum(AmountColumn) as [TotalAmount]   
  2.   from Tablename   
  3.   where Datecolumn between 'Startdate' and 'Enddate'   
  4.   Group By YEAR(Datecolumn), MONTH(Datecolumn)   
  5.   order by MONTH(Datecolumn);   
///////////// C# Code To find Total At footer, 
  1. protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {  
  2.     if (e.Row.RowType == DataControlRowType.DataRow) {  
  3.         storid = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Month").ToString());  
  4.         Double tmpTotal = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "TotalTolling").ToString());  
  5.         qtyTotal += tmpTotal;  
  6.         grQtyTotal += tmpTotal;  
  7.     }  
  8.     if (e.Row.RowType == DataControlRowType.Footer) {  
  9.         Label lblTotalqty = (Label) e.Row.FindControl("lblTotalqty");  
  10.         lblTotalqty.Text = grQtyTotal.ToString();  
  11.     }  
  12. }  
  13. protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e) {  
  14.     bool newRow = false;  
  15.     if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "Month") != null)) {  
  16.         if (storid != Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Month").ToString())) newRow = true;  
  17.     }  
  18.     if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "Month") == null)) {  
  19.         newRow = true;  
  20.         rowIndex = 0;  
  21.     }  
  22.     if (newRow) {  
  23.         GridView GridView1 = (GridView) sender;  
  24.         GridViewRow NewTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);  
  25.         NewTotalRow.Font.Bold = true;  
  26.         NewTotalRow.BackColor = System.Drawing.Color.BurlyWood;  
  27.         NewTotalRow.ForeColor = System.Drawing.Color.Brown;  
  28.         TableCell HeaderCell = new TableCell();  
  29.         HeaderCell.Text = "Sub Total";  
  30.         HeaderCell.HorizontalAlign = HorizontalAlign.Left;  
  31.         HeaderCell.ColumnSpan = 2;  
  32.         NewTotalRow.Cells.Add(HeaderCell);  
  33.         HeaderCell = new TableCell();  
  34.         HeaderCell.HorizontalAlign = HorizontalAlign.Right;  
  35.         HeaderCell.Text = qtyTotal.ToString();  
  36.         NewTotalRow.Cells.Add(HeaderCell);  
  37.         GridView1.Controls[0].Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow);  
  38.         rowIndex++;  
  39.         qtyTotal = 0;  
  40.     }  
  41. }