?
Solved

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

Posted on 2011-03-11
12
Medium Priority
?
717 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:bigmikey88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 35112779
select cast(yearCol as varchar(4)) + right('0'+ cast(monthCol as varchar), 2)  AS ComputedPeriod
from   SomeTable
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35112812
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

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35113045
Why not just use:
<code>
CONVERT(VarChar(06), yourdatecolumn, 112) AS Computed_Period
</code>
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35113054
Sorry, missed the 2 separate columns. :-/  Why do poeple do that? ;-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35113241
Yeah, I prefer date/time columns too. Makes it much easier to convert to whatever format you need.
0
 

Author Comment

by:bigmikey88
ID: 35113281
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.
0
 

Author Comment

by:bigmikey88
ID: 35113424
_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.

0
 

Author Comment

by:bigmikey88
ID: 35113522
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>
0
 

Author Comment

by:bigmikey88
ID: 35113578
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>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35113668

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.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35117730
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35118674
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question