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.docdat
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.
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.docdat
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.
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.
Then why not use the DATENAME() function ?
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.
hi,
Please try this,
Please try this,
SELECT CONVERT(VARCHAR,DATENAME(mm,GETDATE()))
Ebcidic,
For a function such as DATENAME() that already returns a character data type, what would be the advantage of converting to varchar?
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.docdat
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.
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.docdat
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
select
sum(case when datedif = DATEPART(MM, GETDATE()) then quantity else 0 end) as month
...........