July 17, 2017 at 7:54 am
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!
July 17, 2017 at 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).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 17, 2017 at 8:20 am
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
July 17, 2017 at 8:34 am
Thom A - Monday, July 17, 2017 8:06 AMNested 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!
July 17, 2017 at 10:39 am
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;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply