Converting Month Value to Month? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Converting Month Value to Month?

Author

Topic

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 09:31:21

Hey Everyone!

I've had great success with this website and appreciate all of the responses I get. Here is another question for you guys..

I have a query with months, the average difference date (information pulled from another table) and the month value. All in all, a 3-column table.

Is there a way to convert my table into a two column table? I want to keep the month value because it keeps my information in ascending order, however, instead of "1,2,3..etc" I want the numbers to say "Jan, Feb, Mar.."

Here is what I have:

Here is what I want it to look like (keeping the months in ascending order in case I need to make a chart)

Here is my CURRENT SQL:

SELECT T.month_value, T.month, T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;

Any suggestions?

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-20 : 09:40:36

do i need to separate the table in every month???

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 09:51:49

quote:
Originally posted by wormz666

do i need to separate the table in every month???

What?
I just wanted to know if there was a way to type something in SQL view to combine the two (the month value and month)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:12:51

quote:
Originally posted by desperate

quote:
Originally posted by wormz666

do i need to separate the table in every month???

What?
I just wanted to know if there was a way to type something in SQL view to combine the two (the month value and month)

did you mean this?


SELECT  T.month, T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 10:16:25

quote:

Originally posted by visakh16

quote:
Originally posted by desperate

quote:
Originally posted by wormz666

do i need to separate the table in every month???

What?
I just wanted to know if there was a way to type something in SQL view to combine the two (the month value and month)

did you mean this?


SELECT  T.month, T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;

Well, that is the original SQL but I have it on union for something else.. Maybe I can take the unions out and start fresh. Is there a way to combine the month & month value into one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:19:44

just use SELECT T.month + CAST(T.month_value AS varchar(2))...

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 10:34:19

quote:
Originally posted by visakh16

just use SELECT T.month + CAST(T.month_value AS varchar(2))...

I got a "Syntax error (missing operator) in query expression 'T.month + CAST(T.month_value AS varchar(2))'."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:42:36

[code]SELECT T.month + CAST(T.month_value AS varchar(2)), T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;[/code]

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 10:49:22

quote:

Originally posted by visakh16

SELECT  T.month + CAST(T.month_value AS varchar(2)), T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;

Got the same exact erorr :( this is tough

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:52:46

[code]SELECT T.month + CAST(T.month_value AS varchar(2)), T.[AvgOfDIFF DATE]
FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION
SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION
SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION
SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION
SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION
SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION
SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION
SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION
SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) AS T
ORDER BY T.month_value;
[/code]

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 11:13:42

quote:

Originally posted by visakh16

SELECT  T.month + CAST(T.month_value AS varchar(2)), T.[AvgOfDIFF DATE]
FROM 
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION 
SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION 
SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION 
SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION 
SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION 
SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION 
SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION 
SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION 
SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) AS T
ORDER BY T.month_value;

Same error, it keeps highlighting the "AS" part in the select line

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:16:07

its working for me. i dont know which tool you're using.

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 11:38:52

quote:
Originally posted by visakh16

its working for me. i dont know which tool you're using.

Yeah, I keep getting the same error

I appreciate the help anyway!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:41:40

are you using sql mgmnt studio?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-20 : 12:13:26

You can continue to use Visakh's solution which I think makes the most sense since you already have the number to order by. But, another (probably bad) option is to do some conversions and order using native datetime values:

DECLARE @Yak TABLE (Val VARCHAR(3))

INSERT @Yak
SELECT 'Jan' UNION ALL
SELECT 'Feb' UNION ALL
SELECT 'Mar' UNION ALL
SELECT 'Apr'
-- etc..

SELECT *
FROM @Yak
ORDER BY MONTH(CAST('01 ' + Val + ' 1900' AS DATETIME))

EDIT: You don't really need to use the MONTH function, but I included it in case it helped make the example more clear.

desperate
Starting Member

21 Posts

Posted - 2008-11-20 : 12:52:54

quote:
Originally posted by Lamprey

You can continue to use Visakh's solution which I think makes the most sense since you already have the number to order by. But, another (probably bad) option is to do some conversions and order using native datetime values:
DECLARE @Yak TABLE (Val VARCHAR(3))

INSERT @Yak
SELECT 'Jan' UNION ALL
SELECT 'Feb' UNION ALL
SELECT 'Mar' UNION ALL
SELECT 'Apr'
-- etc..

SELECT *
FROM @Yak
ORDER BY MONTH(CAST('01 ' + Val + ' 1900' AS DATETIME))
EDIT: You don't really need to use the MONTH function, but I included it in case it helped make the example more clear.

I'm not too sure what that means :(
I'm using SQL regular view in Microsoft Access

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 01:50:04

quote:
Originally posted by desperate

quote:
Originally posted by Lamprey

You can continue to use Visakh's solution which I think makes the most sense since you already have the number to order by. But, another (probably bad) option is to do some conversions and order using native datetime values:
DECLARE @Yak TABLE (Val VARCHAR(3))

INSERT @Yak
SELECT 'Jan' UNION ALL
SELECT 'Feb' UNION ALL
SELECT 'Mar' UNION ALL
SELECT 'Apr'
-- etc..

SELECT *
FROM @Yak
ORDER BY MONTH(CAST('01 ' + Val + ' 1900' AS DATETIME))
EDIT: You don't really need to use the MONTH function, but I included it in case it helped make the example more clear.

I'm not too sure what that means :(
I'm using SQL regular view in Microsoft Access

Aha..i guess thats the problem.
You may try you luck by posting this in aces forums then.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-21 : 02:49:12

order by dateadd("d",0,month& " 2008")

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources