Introduction
In this SQL
Server article I will explain how to write query to get monthly (month wise) data or
year wise data from database.
Description
In previous articles I explained Query to get Exclude weekends in SQL Query, While Loop Example in SQL Server, Find First and last day of current month in SQL, Convert Rows to Columns in SQL, Joins in SQL Server and many articles relating to SQL
Server. Now I will explain how to write query to get
month wise data or year wise data from database in SQL
Server.
If we want to get data based on month wise or year wise
we need to write the query like as shown below
SELECT YEAR(DateColumn) AS [Year],MONTH(DateColumn) AS [Month],COUNT(*) from yourtable Group
By YEAR(DateColumn) MONTH(DateColumn)
|
If you want to see it in complete example first design
one table (UserInfo) with two columns in database and enter some dummy
data like as show below
Type
|
CreateDate
|
Technology
|
2012-11-18 09:00:00:00
|
Social Network
|
2012-11-28 09:29:00:00
|
Education
|
2012-11-30 10:30:20:00
|
Politics
|
2012-10-04 04:20:33:59
|
Software
|
2012-10-03 04:20:33:59
|
Hardware
|
2012-10-02 04:20:33:59
|
Teaching
|
2012-10-01 04:20:33:59
|
Tech Support
|
2012-09-28 09:00:00:00
|
Customer Support
|
2012-09-10 09:00:00:00
|
Now from above table we need to get the records month
wise and year wise for that we need to write the query like as shown
DECLARE @temp TABLE (ID INT,Location varchar(120))
INSERT INTO @temp VALUES(1,'Near to Men''s Hostel')
SELECT * FROM @temp
|
Once we run above query we will get output like as
shown below
Output:
Year
|
Month
|
Count
|
2012
|
9
|
2
|
2012
|
10
|
4
|
2012
|
11
|
3
|
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
4 comments :
how to show nonthly report click on the month radiobotton
Hi
Actually i want to fetch data date wise. for that purpose i take two Textbox with Ajax Calender. and entering date in same Format.="dd-MM-yyyy"
and for retrieve data i am using Select Query
Here is click Code
"
protected void Button1_Click(object sender, EventArgs e)
{
lblMsg.Text = "";
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
cnn.Open();
SqlDataAdapter da = new SqlDataAdapter("select Id, Project, Date, Name, Email, Phone from Query where Date Between '" + Convert.ToString(txtDateFrom.Text) + "' and '" + Convert.ToString(txtDateTo.Text) + "'", cnn);
SqlCommandBuilder cm = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "Query");
if (ds.Tables["Query"].Rows.Count > 0)
{
GridView1.DataSource = ds.Tables["Query"];
GridView1.DataBind();
}
else lblMsg.Text = "Record Not Found.....Please Select Exact Date..!!! ";
}
"
with this select query i am able to fatch data but its not coming in property way
like.. if i select data 01-10-13 to 01-12-2013. then its showing all data which is stored in table.. its not showing data as particular selected date
can i know in which line u r getting erroe
How to Get Monthly Data in Linq by joining multiple Tables.
Based on the Above SQL Query.
Note: Only a member of this blog may post a comment.