asked on

How to make computed column that concatenates year and month columns but shows months as 2 digits

In Sql Server 2008 query...

How do I create a computed column that will concatenate contents of two integer columns into a new integer

here are the 2 existing columns

Year Month
2009 1
2009 2
2009 3
....
2009 10
2009 11
2009 12

I want the computed column to show

Year Month Computed Period
2009 1 200901
2009 2 200902
2009 3 200903
....
2009 10 200910
2009 11 200911
2009 12 200912

I need it to add a leading zero to any month with single digit.

I'd like to use this computed column in the Select statement and Where statement

Thx

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

I'd put it into a VIEW, so it could be re-used. Then use the VIEW in your query instead of the table.

... 200910

You might also consider making it a date. Then you can use date functions

SELECT convert(datetime,   CAST(yearCol as varchar(4)) +'-'+  RIGHT('0'+ CAST(monthCol as varchar), 2) +'-01', 120) AS FirstDayOfPeriod
FROM	SomeTable

Open in new window

8080_Diver

Why not just use:
<code>
CONVERT(VarChar(06), yourdatecolumn, 112) AS Computed_Period
</code>

8080_Diver

Sorry, missed the 2 separate columns. :-/ Why do poeple do that? ;-)

_agx_

Yeah, I prefer date/time columns too. Makes it much easier to convert to whatever format you need.

Michael Robinson

ASKER

As to why use a separate column for year and month, someone suggested that here at experts-exchange and I am trying to see if it makes life easier.

I will experiment with the suggestions here and get back to you.

Thx for the help.

Michael Robinson

ASKER

_agx_

Once I get ComputedPeriod which is now varchar, how can I convert it to an integer, so I can use it in the WHERE clause.

Michael Robinson

ASKER

here is the query but I keep getting an error

<cfquery name="GetEarnings" datasource="mydb" dbtype="ODBC">
SELECT Period_Year, Period_Month, Amount AS Earnings, cast(Period_Year as varchar(4)) + right('0'+ cast(Period_Month as varchar), 2) AS ComputedPeriod
FROM mytable
Where ComputedPeriod >= #StartPeriod# AND ComputedPeriod <= #EndPeriod#
Order By Period_Year, Period_Month
</cfquery>

Michael Robinson

ASKER

this works

guess i could not use the computed column value in the where clause

<cfquery name="GetEarnings" datasource="mydb" dbtype="ODBC">
SELECT Period_Year, Period_Month, Amount AS Earnings, cast(Period_Year as varchar(4)) + right('0'+ cast(Period_Month as varchar), 2) AS ComputedPeriod
FROM mytable
Where AFID = '#Session.AFID#' AND
cast(Period_Year as varchar(4)) + right('0'+ cast(Period_Month as varchar), 2) >= #StartPeriod# AND
cast(Period_Year as varchar(4)) + right('0'+ cast(Period_Month as varchar), 2) <= #EndPeriod#

Order By Period_Year, Period_Month
</cfquery>

_agx_

Like I said, you'd be better off creating a VIEW with this computed column. Then use the VIEW in your query just like any other table

WHERE ComputedPeriod = '201109'

guess i could not use the computed column value in the where clause

No, you just need to convert it back to an integer like you thought.

ie
WHERE cast(
cast(Period_Year as varchar(4)) + right('0'+ cast(Period_Month as varchar), 2)
AS integer) >= #someValue#
... etc...

BUT it's getting pretty convoluted at this point. It seems like you're trying force numeric columns to act as date columns. Based on your last 2 questions, I'd say you should be using a date/time column instead.

8080_Diver

You could also wrap the basic query you created as the data source for an outer SELECT {list of columns} and then you could use the computed column in your where clause. It is sort of like using a view but it doesn't involve actually creating a view.

_agx_

True, a derived table works too. I got the impression there were multiple queries involved. In which case a single re-usable view might be better. But ultimately it might be a moot point. From their last two questions it sounds like they'd be better off using a single date/time column instead of two separate numeric columns.