Brain dead, can't figure this one out.

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 2000 Forums

SQL Server Development (2000)

Brain dead, can't figure this one out.

Author

Topic

trellend
Starting Member

9 Posts

Posted - 2010-04-01 : 12:07:54

I have a Table with the following info.

Tour: Tour_Host, Tour_Date

The results I'm looking for are:

Jill, March, 7
Joe, March, 25
Joe, April, 10

I can get the data for a single month via:

select Tour_host, count(1) as hosted from tour where tour_date >= '3/1/2010' and tour_date < '4/1/2010' group by tour_host order by hosted desc

Results: Jill, 7; Joe, 25

But I want the whole year grouped by month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 12:18:06

just pass an integer @year value to below query(2009,2010,..) and it will give you the results monthwise as you want

select Tour_host,MONTH(tour_date) AS Mnth, count(1) as hosted from tour 
where tour_date >=DATEADD(yy,@year-1900,0) and tour_date < DATEADD(yy,@year-1899,0)
group by tour_host,MONTH(tour_date) 
order by hosted desc

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

trellend
Starting Member

9 Posts

Posted - 2010-04-01 : 13:59:06

That does it! Thank you, I'll leave converting the #'s back to "March, April" for me...

TC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:02:25

thats simple. just change MONTH(tour_date) to DATENAME(mm,tour_date) in above query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources