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 2008 Forums
 Transact-SQL (2008)
 count records for current year

Author  Topic 

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-03-27 : 14:14:00
I need to count records for current year. Field is called EventDate and is a Date format.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 14:23:11
SELECT YEAR(EventDate), COUNT(*)
FROM yourTable
GROUP BY YEAR(EventDate)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-03-27 : 14:30:29
Works perfect. Thanks for the quick response.
Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-03-27 : 16:55:22
One more thing
How do I just pull current year?
The above gives me a count for each year, which is needed but I also just want current year.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 20:04:06
[code]
SELECT YEAR(EventDate), COUNT(*)
FROM yourTable
WHERE YEAR(EventDate)=YEAR(GETDATE())
GROUP BY YEAR(EventDate)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-03-27 : 20:59:46
Didn't notice till now but what if count is null for current year? How would I show that as 0 ?

Thanks so much for help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 21:05:09
for that you need a calendar table and left join to that. something like

SELECT YEAR(f.[Date]),COUNT(t.EventDate)
FROM dbo.CalendarTable(DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0),DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)-1,0,0)f
LEFT JOIN YourTable t
ON t.EventDate = f.[Date]
GROUP BY YEAR(f.[Date])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 21:08:16
see the calendar table function below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-27 : 21:13:09
use a number table and left join to your query result

select n.number as [YEAR],
isnull(r.cnt, 0) as [cnt]
from number_table n
left join
(
SELECT YEAR(EventDate) as [Year], COUNT(*) as [cnt]
FROM yourTable
GROUP BY YEAR(EventDate)
) r on n.number = r.[Year]
where n.number between 2010 and 2010 -- change the year range accordingly


number table is just a single column table that contains numbers. If you don't have one, you can consider create one.
Alternatively you can use master..spt_values or http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-03-27 : 21:18:16
WOW never heard of calendar table, but reading about tells me it is much needed in my databases. Thanks again.
Go to Top of Page
   

- Advertisement -