Using Columns in the table to pass a variable val

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

SQL Server 2008 Forums

Transact-SQL (2008)

Using Columns in the table to pass a variable val

Author

Topic

alpsdev
Starting Member

3 Posts

Posted - 2012-03-29 : 09:41:00

HI,

I have a table with values Project, Revenue_1, Revenue_2... Revenue_12 each column representing Revenue figures for months 1 to 12.

I have to do a currency convert for these Revenue figures. In this case for each column the date for picking the conversion rate will be different.

Ex: For Revenue_1 column, i have to pass date as '01-Jan-2012'. How do I achieve this. I cant use CASE since it requires an expression. I can write individual sections for each month and use UNION ALL, but I want to know if there is any other alternative or simpler way to do this.

Also i dont want to do a transpose to convert columns to row at this level since this database doesnt support UNPIVOT.

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 09:47:27

First...it's not clear what you want...

Post the DDL, sample data and expected results

Second, " I cant use CASE since it requires an expression"

Why the hell not?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

alpsdev
Starting Member

3 Posts

Posted - 2012-03-29 : 11:09:37

I need to manipulate the variable passed based on the "column name" and not the column value.

Example : TableA has columns as below
PROJECT_ID BASE_CURRENCY REVENUE_1 REVENUE_2
100 INR 1500 2000
101 CAD 2500 3000

TableB has columns
DATE FROM_CURRENCY TO_CURRENCY RATE
2012-JAN-31 INR USD 45
2012-FEB-20 INR USD 46

now for Revenue_1 column I have to pass the DATE from TableB as 2012-JAN-31. So to identify REVENUE_1 as JAN, I have to use the column_name here in the CASE. How can I do that?

IF REVENUE_1 THEN (SELECT DATE FROM TABLEB WHERE DATEPART(MONTH,DATE)='01'...)

The query as below:

SELECT
A.PROJECT_ID
, A.REVENUE_1 * B.RATE AS JAN_REV_USD
, A.REVENUE_2 * B.RATE AS FEB_REV_USD
FROM TABLE1 A JOIN TABLE2 B
ON A.BASE_CURRENCY = B.FROM_CURRENCY
AND B.TO_CURRENCY='USD'
AND B.DATE = CASE WHEN 'REVENUE_1' ????
THEN (SELECT MAX(DATE)
FROM TABLEB
WHERE DATEPART(MONTH,DATE)='01'
AND DATEPART(YEAR,DATE)='2012')
ELSE WHEN 'REVENUE_2' THEN ...
END

How can I use column name here (REVENUE_1, REVENUE_2) instead of Column value? I have an alternative to write separate unions for each month,, which goes very lengthy.

Possibly what I'm thinking is not possible :) Thanks for replying.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 12:35:50

sounds like UNPIVOT for me to get revenue column values as rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 12:58:06

I guess you could use dynamic SQL

EXEC(@sql)

But I still don't know what you are talking about

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

alpsdev
Starting Member

3 Posts

Posted - 2012-03-30 : 07:44:27

@visakh16, yes it would hve been possible with UNPIVOT, but this database is not compatible to use this command.

@brett, thanks, i couldnt have better explained it than earlier. Im however working on alternatives.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-30 : 17:40:20

quote:
Originally posted by alpsdev

@visakh16, yes it would hve been possible with UNPIVOT, but this database is not compatible to use this command.

@brett, thanks, i couldnt have better explained it than earlier. Im however working on alternatives.

you mean compatibility level is 80 or below

then you can use set of UNION ALL statements to simulate UNPIVOT action

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources