June 15, 2010 at 12:49 pm
I have this case statement as part of my query - BUT it only evaluates the first line..
CASE WHEN cma_completed_date2>cma_completed_date1 THEN CMA_AS_IS_VALUE2
WHEN cma_completed_date3>cma_completed_date2 THEN CMA_AS_IS_VALUE3
WHEN cma_completed_date4>cma_completed_date3 THEN CMA_AS_IS_VALUE4
--WHEN cma_completed_date5>cma_completed_date4 THEN CMA_AS_IS_VALUE5
END AS 'TEST'
why would only read the first line and stop?
June 15, 2010 at 2:03 pm
The most probable reason: each and every row in your query contains values where cma_completed_date2 > cma_completed_date1.
A CASE statement will set the return value according to the first (sequential) true WHEN statement.
What are you trying to do? Please post some sample data ina a ready to use format as described in the first link in my signature. We might be able to help you solving the puzzle.
June 15, 2010 at 2:46 pm
MARKET VALUE 1MARKET VALUE DATE 1MARKET VALUE 2MARKET VALUE DATE 2MARKET VALUE 3MARKET VALUE DATE 3MARKET VALUE 4MARKET VALUE DATE 4MARKET VALUE 5MARKET VALUE DATE 5
1003/2/092003/3/093003/4/094003/5/095003/6/09
Hi Imu92,
I have above table and I want to use a nested if statement in SQL to obtain the following results. Below is the logic:
1. if market_value_date2>market_value_date1 then market_value_2 else market_value_1
2. if market_value_date3>market_value_date2 then market_value_3 else market_value_2 and so on.
Ultimately, the output for the above table in my column should be Market_Value_5 since it has the most recent date.
How do I do this? Any help would be appreciated. Thank you.
June 15, 2010 at 3:31 pm
Seems like you've got trapped by a "semi-optimal" table design...
Storing different dates and corresponding values in several columns violates normalization. As a "side effect" it makes queris like you're trying to do much more complicated.
What I would do is to redesign the table structure to have a MarketValue and a MarkedValueDate leading to multiple rows per id (or whatever identifies the row).
If that's not possible I'd create an unpivoted view (see UNPIVOT in BOL for details).
Then the query would be rather simple...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply