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
 General SQL Server Forums
 New to SQL Server Programming
 month		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

279 Posts
Posted - 07/30/2012 :  23:55:10  Show Profile  Reply with Quote
select
SUM(1) as total
,MONTH(dateon) as month
,category
from tableA
where dateon between '2012-05-01' and '2012-06-30'
group by MONTH(dateon),category

i get the result as below:
total month category
20 5 categoryA
40 5 categoryB
60 6 categoryA

how can i change the month to Name?

Month
May
May
June

visakh16
Very Important crosS Applying yaK Herder

India
47133 Posts
Posted - 07/30/2012 :  23:58:37  Show Profile  Reply with Quote 

select
SUM(1) as total
,DATENAME(mm,dateon) as month
,category
from tableA
where dateon between '2012-05-01' and '2012-06-30'
group by DATENAME(mm,dateon),category


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

peace
Constraint Violating Yak Guru

279 Posts
Posted - 07/31/2012 :  00:10:54  Show Profile  Reply with Quote
Thanks it works

Another doubt, im declaring the dateon as below:

declare @dateon datetime

how can i declare in month?

... where dateon in ('June','July','May')
Go to Top of Page

LoztInSpace
Aged Yak Warrior

876 Posts
Posted - 07/31/2012 :  09:38:09  Show Profile  Reply with Quote
If your months are sequential then just trim up your dates to be within the range you're after. You can find examples of this all over. Here's a quick one from Google:
http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
replace GetDate() with your parameter.
If the months are not sequential you need to look at how you intend to pass the required list in, but you'll most likely be using where DATEPART(mm, dateon) in .....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47133 Posts
Posted - 07/31/2012 :  09:41:36  Show Profile  Reply with Quote
quote:
Originally posted by peace

Thanks it works

Another doubt, im declaring the dateon as below:

declare @dateon datetime

how can i declare in month?

... where dateon in ('June','July','May')


are you planning to pass a single value of month or can it be comma separated list as shown above?


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

peace
Constraint Violating Yak Guru

279 Posts
Posted - 07/31/2012 :  21:52:16  Show Profile  Reply with Quote
is single value
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47133 Posts
Posted - 07/31/2012 :  22:50:01  Show Profile  Reply with Quote 

declare @Monthon varchar(20)

...
where dateon =@Monthon


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.12 seconds. Powered By: Snitz Forums 2000