Solved

Case statement in SSIS expression lingo

Posted on 2011-03-01
4
607 Views
Last Modified: 2012-05-11
What’s the expression language within a derived column transform to do CASE type stuff…

Basically, I’m adding a column that is a retainer percent that adjusts client spend based on date so if

Tape_aired < 1/31/11 then RetainerPerc                                                               =  .97 for that row
Tape_aired between 1/31/11 and 2/15/11 then RetainerPerc                      = .984 for that row
Tape_aired between 2/15/11 and 3/30/11 then RetainerPerc                      = .992 for that row


THanks all as this site has been amazing for me...
0
Comment
Question by:Gray5452
  • 3
4 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 35007623
Hi

This should help
http://www.itcouple.com/ssis-if.aspx

You can nest them using brackets... Let me know if you need an example (I actually need to post an example the above page with nesting).

Hope that helps
Emil
0
 

Author Comment

by:Gray5452
ID: 35007785
thanks, that is much appreciated Emil,,,would the brackets just go back to back?
0
 
LVL 10

Expert Comment

by:itcouple
ID: 35007904
I'm not sure if typed it correctly but below should give you the answer:

(Field==2?1:(Field==3?1:2))

Regards
Emil
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 35008588
Hi

I updated my link (will publish tonight) and below is an example for you

(DateField < "2011-01-01" ? 0.5 : (DateField >= "2011-01-01" && DateField <= "2011-02-28" ? 0.75 : 1))

I haven't tested in with actual table so you might have to do some cast to date. Also third condition I put as else instead of your final range so you might want to change that.

Regards
Emil
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best Query To Insert One Year Calendar 6 44
xpath sql query 2008 8 41
triggered use of sp_send_dbmail failure 2 22
Update data using formula 22 12
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now