CASE statement with IN clause

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  October 6, 2010 at 1:23 pm

  #227317

  Hi all,

  I am using a query in my report and have isssue in doing multi select with an amount field.

  Actually I want to have a prompt like this:

  Select ANY Amount range:-

  > 8500

  < -8500

  BETWEEN -8500 AND 8500

  The query is simple with case statement to run for either one of the range selected.

  But now I want to also run by multi selection on Ranges with an OR statement.

  I am able to run for either of the range selected but I am not able to run for more than one range selected.

  Any help on this.

  Here is my sample code.

  create table dbo.test

  (

  year int,

  month int,

  Name varchar(12),

  amount int)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 1, 'ABC', -9500)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 2, 'XYZ', 7500)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 3, 'DEF', 5500)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 4, 'WWW', -11500)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 5, 'VVV', 9000)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 6, 'JJJ', 3000)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 7, 'TTT', -10000)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 8, 'YYY', 6000)

  INSERT INTO test ( year,month,Name,amount) VALUES ( 2010, 9, 'PPP', 8700)

  select * from test

  declare @range int

  set @range = 3 --Keep changing to 1/ 2/ or 3. How to run for 1 OR 2....

  SELECT YEAR,MONTH,

  SUM(amount) AS NET_AMOUNT

  FROM TEST

  WHERE

  YEAR = 2010

  AND MONTH in ( 1,2,3,4,5,6 )

  group by YEAR,MONTH

  HAVING

  (1=

  case WHEN @range = 1 THEN (CASE WHEN SUM(amount) > 8500 THEN 1 ELSE 0 END)

  WHEN @range= 2 THEN (CASE WHEN SUM(amount) < -8500 THEN 1 ELSE 0 END)

  WHEN @range = 3 THEN (CASE WHEN SUM(amount) BETWEEN -8500 AND 8500 THEN 1 ELSE 0 END)

  ELSE 0 END)

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  October 12, 2010 at 2:51 pm

  #1235877

  I don't understand how this applies to my given data...??

  In my given data is there any way to do this..

  Please let me know a way to do this using my data. or let me know if my given data is unclear.

  Thanks

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]