Nesting an IIF function inside a MAX function

user_3621731 0
Viewed 416

I'm tasked with analyzing a T-SQL function that repeatedly nests an IIF function inside a MAX function. Am I missing something or does this simply not change the output at all?

DECLARE @firstVal INT = 1;
SELECT [NotWrappedIff] = IIF(1=2, @firstVal, NULL)
SELECT [MaxWrappedIff] = MAX(IIF(1=2, @firstVal, NULL))

The actual code has a additional isnull, and dateadd functions nested, but the structure is the same. The false value of the nested IIFs are all NULL. Actual code:

SomeIdToKeep = max(iif(isnull(SomeStartTime, SomeOtherStartTime) < @parmStartTime and dateadd(hour, HourIntVal, isnull(SomeStartTime, SomeOtherStartTime)) >= @parmStartTime, SomeId, null))
tsql sql-server sql
1 Answers

Your have over simplified the example which is why its not making sense.

The code itself performs a conditional aggregation, i.e. it returns the MAX value out of the rows which meet the provided condition determined by IIF.

This can be demonstrated as follows:

declare @Test table (id int, StartDate date, [Value] int);

insert into @Test (id, StartDate, [Value])
values
(1, '01-Feb-2021', 10),
(2, '02-Feb-2021', 20),
(3, '03-Feb-2021', 30);

select
    max([Value]) TrueMax
    , max(iif(StartDate < '03-Feb-2021', [Value], null)) ConditionalMax
from @Test;

Which returns:

TrueMax ConditionalMax
30 20

Note: You can be sure that you are aggregating, because otherwise attempting to use MAX throws an error.

user_11274280
Related