select record for each month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select record for each month

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-16 : 08:19:52

Hi,
I have one field created_date in my table. It has all dates that the record created.
Now i want to select the records in each month of this year and count of records in each month..

How to do this ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-16 : 08:49:42

[code]SELECT
DATEADD(mm,DATEDIFF(mm,0,create_date),0) AS FirstDayofCreatedMonth,
COUNT(*)
FROM
YourTable
GROUP BY
DATEADD(mm,DATEDIFF(mm,0,create_date),0)
[/code]You can use Year and month functions if you want to just get the year and month rather than the first of the month in the above query

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-16 : 09:04:29

Thank you sunitabeck for your reply.. It partially fulfilled my need ..This is my req output

ID Amount Date Count
1 100$ 1/1/2012 2
2 150$ 25/1/2012 2
3 100$ 2/2/2012 1
4 200$ 7/7/2012 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-16 : 09:40:41

quote:
Originally posted by jafrywilson

Thank you sunitabeck for your reply.. It partially fulfilled my need ..This is my req output

ID Amount Date Count
1 100$ 1/1/2012 2
2 150$ 25/1/2012 2
3 100$ 2/2/2012 1
4 200$ 7/7/2012 1

SELECT
	ID,
	Amount,
	[Date],
	COUNT(*) OVER(PARTITION BY DATEADD(mm,DATEDIFF(mm,0,[Date]),0)) AS [Count]
FROM
	YourTable;

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-16 : 10:30:27

It works... Thank you so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources