asked on 5/13/2011

Sql query syntax month as field name

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.

Microsoft SQL Server 2008 Microsoft SQL Server 2005

Last Comment

notasgoodasyou

5/16/2011

Ephraim Wangoya

5/13/2011

you can use date part

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

notasgoodasyou

5/14/2011

ASKER

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.

Anthony Perkins

5/14/2011

Then why not use the DATENAME() function ?

Kevin Cross

5/14/2011

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.

Ebcidic

5/14/2011

hi,
Please try this,

SELECT CONVERT(VARCHAR,DATENAME(mm,GETDATE()))

Open in new window

Anthony Perkins

5/15/2011

Ebcidic,

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

prajapati84

5/15/2011

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 Cross

5/15/2011

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.