Solved

putting IF logic into a calculated column?

Posted on 2010-08-17
7
262 Views
Last Modified: 2012-05-10
With the below query, is it possible to insert some logic which would output a text string into the next column depending on the value of the last column (which is calculated)

e.g. "small" if value <= 10, otherwise "large"

Or do we need to do some logic in TSQL for this?
i.e. open up a cursor, store the value of col#4 in a variable, do our logic on it, and output a string using PRINT depending on its value ?

cheers!
select EA.ex_pi_eval_answer_school_id, 

SUM(CAST(EA.ex_pi_eval_answer_response AS DECIMAL)), 

COUNT(EA.ex_pi_eval_answer_response),

SUM(CAST(EA.ex_pi_eval_answer_response AS DECIMAL)) / COUNT(EA.ex_pi_eval_answer_response)



from tbl_ex_ProgramImpact_eval_answers EA



where EA.ex_pi_eval_answer_ProgramObjective_id = 1



group by EA.ex_pi_eval_answer_school_id

Open in new window

0
Comment
Question by:zorba111
  • 4
  • 2
7 Comments
 

Author Comment

by:zorba111
ID: 33455172
NB typical output from the current query is like:

2036449      19      5      3.8
4010885      22      10      2.2
1116159      23      5      4.6
2046646      23      5      4.6
4036081      23      5      4.6
1016647      22      5      4.4
2036149      21      5      4.2
4230165      20      5      4

(excel, which I've used as an intermediary to copy this data, has stripped out non-significant trailing zeros)
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 400 total points
ID: 33455212
you can use a CASE Statement Like

OUTPUTColumn = CASE WHEN Value<=10 THEN 'Small' ELSE 'Large' END

The below blog has some good explanation with examples
http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/
0
 

Author Comment

by:zorba111
ID: 33455333
@vdr1620

Hi this is great, however having read up on CASE WHEN, I fear it will not work.

I had simplified my problem somewhat, so its not your fault!

There are actually three ranges (NOT two) that the values could fall into, and for each value, a different string gets output :-(
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 400 total points
ID: 33455374
Not a problem..you can use

CASE WHEN (condition) THEN Statement
          WHEN (condition) THEN Statement
           WHEN (condition) THEN Statement END

OUTPUTColumn = CASE WHEN Value<=10 THEN 'Small'
                                        WHEN Value >10 THEN 'Large'
                                        WHEN Value >100 THEN 'SomeotherValue'        END

0
 

Author Comment

by:zorba111
ID: 33455382
@vdr1620

hmm, having looked at Pinal Dave's page (the link you kindly referred me to), I think now that all may not be lost!

I will report back....
0
 

Author Comment

by:zorba111
ID: 33455419
ok, it wasn't in his blog, but in MSDN I found this usage....

SELECT    'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 33460045
You can also do something like this, if you need to:

SELECT
    CASE WHEN price IS NULL THEN '...'
              WHEN price < 10 THEN '...'
              WHEN price >= 10 AND price < 20 THEN '...'
              ELSE '...' END +
    ',' +
    CASE
              WHEN size < 10 THEN 'Small'
              WHEN size > 10 AND size < 20 THEN 'Medium'
              WHEN size >= 20 THEN 'Large'
              ELSE 'Unknown size' END
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 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

25 Experts available now in Live!

Get 1:1 Help Now