SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 count records for current year		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chapm4
Yak Posting Veteran

58 Posts
Posted - 03/27/2012 :  14:14:00  Show Profile  Reply with Quote
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 - 03/27/2012 :  14:23:11  Show Profile  Reply with Quote
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 - 03/27/2012 :  14:30:29  Show Profile  Reply with Quote
Works perfect. Thanks for the quick response.
Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts
Posted - 03/27/2012 :  16:55:22  Show Profile  Reply with Quote
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

India
47189 Posts
Posted - 03/27/2012 :  20:04:06  Show Profile  Reply with Quote 

SELECT YEAR(EventDate), COUNT(*)
FROM yourTable
WHERE YEAR(EventDate)=YEAR(GETDATE())
GROUP BY YEAR(EventDate)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts
Posted - 03/27/2012 :  20:59:46  Show Profile  Reply with Quote
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

India
47189 Posts
Posted - 03/27/2012 :  21:05:09  Show Profile  Reply with Quote
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

India
47189 Posts
Posted - 03/27/2012 :  21:08:16  Show Profile  Reply with Quote
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)

Singapore
16746 Posts
Posted - 03/27/2012 :  21:13:09  Show Profile  Reply with Quote
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
Time is always against us
Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts
Posted - 03/27/2012 :  21:18:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000