Hi itsjayshah,
I have created a sample which full fill your requirement you need to modify the code according to your need.
SQL
CREATE PROCEDURE GetSumAmount
@date DATETIME
AS
BEGIN
DECLARE @tb_data AS TABLE(id INT,amount INT,tb_date DATETIME)
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (1, 100, CAST(0x0000A7F400000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (2, 1000, CAST(0x0000A7F500000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (3, 200, CAST(0x0000A72000000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (4, 2000, CAST(0x0000A72100000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (5, 500, CAST(0x0000A7F400000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (6, 500, CAST(0x0000A7F400000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (7, 501, CAST(0x0000A7F700000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (8, 900, CAST(0x0000A7FC00000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (9, 900, CAST(0x0000A72100000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (10, 900, CAST(0x0000A71E00000000 AS DateTime))
INSERT @tb_data ([id], [amount], [tb_date]) VALUES (11, 900, CAST(0x0000A71900000000 AS DateTime))
SELECT SUM(amount) amount,tb_date
FROM @tb_data
WHERE tb_date = @date
GROUP BY tb_date
END
HTML
<div>
Select Month:
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem>Select</asp:ListItem>
<asp:ListItem Value="1">January</asp:ListItem>
<asp:ListItem Value="2">February</asp:ListItem>
<asp:ListItem Value="3">March</asp:ListItem>
<asp:ListItem Value="4">April</asp:ListItem>
<asp:ListItem Value="5">May</asp:ListItem>
<asp:ListItem Value="6">June</asp:ListItem>
<asp:ListItem Value="7">July</asp:ListItem>
<asp:ListItem Value="8">August</asp:ListItem>
<asp:ListItem Value="9">September</asp:ListItem>
<asp:ListItem Value="10">October</asp:ListItem>
<asp:ListItem Value="11">November</asp:ListItem>
<asp:ListItem Value="12">December</asp:ListItem>
</asp:DropDownList>
Select Year
<asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem>Select</asp:ListItem>
<asp:ListItem>2010</asp:ListItem>
<asp:ListItem>2011</asp:ListItem>
<asp:ListItem>2012</asp:ListItem>
<asp:ListItem>2013</asp:ListItem>
<asp:ListItem>2014</asp:ListItem>
<asp:ListItem>2015</asp:ListItem>
<asp:ListItem>2016</asp:ListItem>
<asp:ListItem>2017</asp:ListItem>
<asp:ListItem>2018</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:Label ID="lblMessage" ForeColor="Red" Font-Bold="true" runat="server" />
<asp:GridView ID="gvDynamic" runat="server" />
</div>
C#
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (DropDownList1.SelectedValue != "Select" && DropDownList2.SelectedValue != "Select")
{
int year = Convert.ToInt32(DropDownList2.SelectedValue.ToString());
int month = Convert.ToInt32(DropDownList1.SelectedValue.ToString());
DataTable dtDynamicTable = new DataTable();
List<DateTime> daysOfMonth = Enumerable.Range(1, DateTime.DaysInMonth(year, month)) // Days: 1, 2 ... 31 etc.
.Select(day => new DateTime(year, month, day)) // Map each day to a date
.ToList();
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
foreach (DateTime d in daysOfMonth)
{
dtDynamicTable.Columns.Add(d.ToString("dd"));
}
dtDynamicTable.Rows.Add();
using (SqlConnection con = new SqlConnection(strConnString))
{
foreach (DateTime d in daysOfMonth)
{
SqlCommand cmd = new SqlCommand("GetSumAmount");
string seldate = d.ToString("yyyy-MM-dd");
cmd.Parameters.AddWithValue("@date", seldate);
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string dbDate = dr["tb_date"].ToString().Split('/')[1];
dtDynamicTable.Rows[0][dbDate] = dr["amount"].ToString();
dtDynamicTable.AcceptChanges();
}
dr.Close();
con.Close();
}
bool valid = false;
for (int i = 0; i < dtDynamicTable.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dtDynamicTable.Rows[0][i].ToString()))
{
valid = true;
break;
}
}
lblMessage.Text = "";
if (!valid)
{
dtDynamicTable = null;
lblMessage.Text = "No Records Found for Month and Year you Selected!";
}
gvDynamic.DataSource = dtDynamicTable;
gvDynamic.DataBind();
}
}
}
VB.Net
Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs)
If DropDownList1.SelectedValue <> "Select" AndAlso DropDownList2.SelectedValue <> "Select" Then
Dim year As Integer = Convert.ToInt32(DropDownList2.SelectedValue.ToString())
Dim month As Integer = Convert.ToInt32(DropDownList1.SelectedValue.ToString())
Dim dtDynamicTable As New DataTable()
' Days: 1, 2 ... 31 etc.
' Map each day to a date
Dim daysOfMonth As List(Of DateTime) = Enumerable.Range(1, DateTime.DaysInMonth(year, month)).[Select](Function(day) New DateTime(year, month, day)).ToList()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
For Each d As DateTime In daysOfMonth
dtDynamicTable.Columns.Add(d.ToString("dd"))
Next
dtDynamicTable.Rows.Add()
Using con As New SqlConnection(strConnString)
For Each d As DateTime In daysOfMonth
Dim cmd As New SqlCommand("GetSumAmount")
Dim seldate As String = d.ToString("yyyy-MM-dd")
cmd.Parameters.AddWithValue("@date", seldate)
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
Dim dbDate As String = dr("tb_date").ToString().Split("/"c)(1)
dtDynamicTable.Rows(0)(dbDate) = dr("amount").ToString()
dtDynamicTable.AcceptChanges()
End If
dr.Close()
con.Close()
Next
Dim valid As Boolean = False
For i As Integer = 0 To dtDynamicTable.Columns.Count - 1
If Not String.IsNullOrEmpty(dtDynamicTable.Rows(0)(i).ToString()) Then
valid = True
Exit For
End If
Next
lblMessage.Text = ""
If Not valid Then
dtDynamicTable = Nothing
lblMessage.Text = "No Records Found for Month and Year you Selected!"
End If
gvDynamic.DataSource = dtDynamicTable
gvDynamic.DataBind()
End Using
End If
End Sub
ScreenShot
