Comparing values from calculated fields

• meichmann

  SSCommitted

  Points: 1900

  More actions

  July 17, 2017 at 7:54 am

  #339023

  Hi Everyone!
       I have a SQL stored procedure that calculates three values and places them in separate columns( all three values are of INT data type).  What I need to do is find out which value is largest and use that value:

  Column a value - 100
  Column b value - 300
  Column c value - 200

  I need logic to figure out which is largest and place that in a calculated field on the report.  I'm pretty sure I need to do a nested Iif, but not sure of the structure.

  Thanks Everyone!

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 17, 2017 at 8:06 am

  #1951160

  Nested IIF is one day, yes:
  =IIF(Fields!ColumnA.Value > Fields!ColumnB.Value AND Fields!ColumnA.Value > Fields!ColumnC.Value, "ColumnA", IIF(Fields!ColumnB.Value > Fields!ColumnA.Value AND Fields!ColumnB.Value > Fields!ColumnC.Value, "ColumnB", "ColumnC"))
  You might need to tweak this a little, as I've used > rather than >= (not knowing what you're full logic is).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 17, 2017 at 8:20 am

  #1951165

  using CASE

  DECLARE @yourtable TABLE
(colA INT,
colB INT,
colC INT
);
INSERT INTO @yourtable
VALUES
(100, 300, 200),
(400, 100, 200),
(500, 500, 600)
;

  SELECT *,
   CASE
    WHEN colA > colB AND colA > colC THEN colA
    WHEN colb > colC THEN colb
    ELSE colC
   END AS largevalue
FROM @YourTable;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• meichmann

  SSCommitted

  Points: 1900

  More actions

  July 17, 2017 at 8:34 am

  #1951173

  Thom A - Monday, July 17, 2017 8:06 AM

  Nested IIF is one day, yes:
  =IIF(Fields!ColumnA.Value > Fields!ColumnB.Value AND Fields!ColumnA.Value > Fields!ColumnC.Value, "ColumnA", IIF(Fields!ColumnB.Value > Fields!ColumnA.Value AND Fields!ColumnB.Value > Fields!ColumnC.Value, "ColumnB", "ColumnC"))
  You might need to tweak this a little, as I've used > rather than >= (not knowing what you're full logic is).

  Thank you!  This works great!  I did have to tweak it, since the columns could have the same values so I just changed all the ">" to ">="

  Thank you for your help!

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  July 17, 2017 at 10:39 am

  #1951212

  my favorite way to do this is to use CROSS APPLY to unpivot the data and then just use MAX():
  DECLARE @yourtable TABLE
(colA INT,
colB INT,
colC INT
);

  INSERT INTO @yourtable
VALUES
(100, 300, 200),
(400, 100, 200),
(500, 500, 600)
;

  SELECT yt.*, m.maxval
    FROM @yourtable yt
    CROSS APPLY (SELECT MAX(val) maxval FROM (VALUES (colA),(colB),(colC)) x(val)) m;