Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Query to Get Month wise Data, Year wise Data

Nov 29, 2012
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
In this way we can get month wise or year wise records count from database using SQL Server.

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 RSS subscribe by email Subscribe by Email

2 comments :

Patra Bachchan said...

how to show nonthly report click on the month radiobotton

Dharmendra said...

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

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.