Sql query syntax  month as field name

notasgoodasyou
notasgoodasyou used Ask the Experts™
on
I have the following sql statement that works fine currently.  I would like the sum case field month to actually be the current month.  so when I run this now it should be as may when viwed.  

select
sum(case when datedif = '12' then quantity else 0 end) as month
from
(SELECT     INV1.ItemCode, INV1.WhsCode, OINV.CardCode, OINV.ShipToCode, INV1.SubCatNum, INV1.U_SIF_CustPartRev, INV1.Quantity, OINV.DocDate, datediff(Month,oinv.docdate,CONVERT(DATETIME, '2011-05-02 00:00:00', 102))as DATEDif
FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry
WHERE    (OINV.DocType = 'i') AND (INV1.ItemCode = 'SP009600079-31T') AND (INV1.WhsCode = '10') AND (OINV.CardCode = '9600') AND (OINV.ShipToCode = '32')
)A.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:
you can use date part

select
  sum(case when datedif = DATEPART(MM, GETDATE())  then quantity else 0 end) as month
...........

Author

Commented:
I dont know what your statement does ewangoya but i am getting the same result i had before. I dont want the results to be" as Month" but as the month they represent "May".  I would like to write the statement to populate the field header based on the month.
Top Expert 2012

Commented:
Then why not use the DATENAME() function ?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Hi.  You will need to use dynamic SQL to take the result of DATENAME() suggested above and make that the column alias.  Otherwise, you can use DATENAME() as another column within your query and then in an outer query PIVOT on the monthname.  

Commented:
hi,
 Please try this,
SELECT CONVERT(VARCHAR,DATENAME(mm,GETDATE()))

Open in new window

Top Expert 2012

Commented:
Ebcidic,

For a function such as DATENAME() that already returns a character data type, what would be the advantage of converting to varchar?
Try this:

DECLARE @month_name VARCHAR(15)
SET @month_name = DATENAME(month, getdate())

select
sum(case when datedif = '12' then quantity else 0 end) as @month_name
from
(SELECT     INV1.ItemCode, INV1.WhsCode, OINV.CardCode, OINV.ShipToCode, INV1.SubCatNum, INV1.U_SIF_CustPartRev, INV1.Quantity, OINV.DocDate, datediff(Month,oinv.docdate,CONVERT(DATETIME, '2011-05-02 00:00:00', 102))as DATEDif
FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry
WHERE    (OINV.DocType = 'i') AND (INV1.ItemCode = 'SP009600079-31T') AND (INV1.WhsCode = '10') AND (OINV.CardCode = '9600') AND (OINV.ShipToCode = '32')
)A.  

Note: You can also write dynamic query to obtain the same result in a single statement.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Please advise which version of SQL you have gotten http:#a35763477 to work in.  You need to do this via dynamic SQL if using the dynamic month name as the alias to column.  As stated earlier, an alternative is to have another column that has the month name as a value and then PIVOT by the 12 month names; however, that will yield 12 columns which may not be what is wanted here.  

Another user level/reporting option I have used is to bring the data back to Excel and dynamically display columns there.  i.e., use the presentation layer for actual months and keep dataset generic like [This Month], [Last Month], etc.
G Trurab KhanSnr. Development Manager

Commented:
Try this

 sql1.sql
I tried in my environment, it worked fine (dynamic query)

Try this out:

DECLARE @month_name VARCHAR(15)
SET @month_name = DATENAME(month, getdate())

exec('select
sum(case when datedif = ''12'' then quantity else 0 end) as '+@month_name+'
 from
(SELECT     INV1.ItemCode, INV1.WhsCode, OINV.CardCode, OINV.ShipToCode, INV1.SubCatNum, INV1.U_SIF_CustPartRev, INV1.Quantity, OINV.DocDate, datediff(Month,oinv.docdate,CONVERT(DATETIME, ''2011-05-02 00:00:00'', 102))as DATEDif
FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry
WHERE    (OINV.DocType = ''i'') AND (INV1.ItemCode = ''SP009600079-31T'') AND (INV1.WhsCode = ''10'') AND (OINV.CardCode = ''9600'') AND (OINV.ShipToCode = ''32'')
)A')

Author

Commented:
Thank you all for all the help.  I sql knowlede is very basic and i just chose the answer that worked when i ran it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial