SSIS Derived column IF..ELSE condition

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 2005 Forums

SSIS and Import/Export (2005)

SSIS Derived column IF..ELSE condition

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-30 : 04:53:00

I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that

if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17)

Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17)

ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17)

I am using following expression..

(((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17))

Can somebody tell me whats wrong in it?

I also tried..

IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17))

its also not working :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 05:35:10

(DT_I4)(SUBSTRING( WEEK,5,2)>=1 && SUBSTRING( WEEK,5,2)<=17) ? SUBSTRING( WEEK,5,2)+35) :SUBSTRING( WEEK,5,2)-17)

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

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-30 : 12:57:03

Following expression worked at last..
(DT_I4) SUBSTRING( WEEK,5,2) >=1 && (DT_I4) SUBSTRING( WEEK,5,2) <= 17 ? (DT_I4) (SUBSTRING( WEEK,5,2))+ 35 : (DT_I4) (SUBSTRING( WEEK,5,2))-17
Thanks Deepak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources