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 |
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2012-03-27 : 14:30:29
|
Works perfect. Thanks for the quick response. |
|
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2012-03-27 : 16:55:22
|
One more thingHow 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-27 : 20:04:06
|
[code]SELECT YEAR(EventDate), COUNT(*)FROM yourTableWHERE YEAR(EventDate)=YEAR(GETDATE())GROUP BY YEAR(EventDate)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 likeSELECT 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)fLEFT JOIN YourTable tON t.EventDate = f.[Date]GROUP BY YEAR(f.[Date]) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-27 : 21:13:09
|
use a number table and left join to your query resultselect 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] |
|
|
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. |
|
|
|