I'm not sure if it's something relevant but I made up a test for speed performance between the two solutions and a direct comparison. Turned out (as I expected) that the direct comparison was faster, but the other solutions where really fast.
Here's the test (using Jeff's tips)
----===== Declare some obviously named variables
--DECLARE @NumberOfRows INT,
-- @StartDate DATETIME,
-- @EndDate DATETIME,
-- @Days INT --This is still the "range"
--;
----===== Preset the variables to known values
-- SELECT @NumberOfRows = 1000000,
-- @StartDate = '2010', --Inclusive
-- @EndDate = '2020', --Exclusive
-- @Days = DATEDIFF(dd,@StartDate,@EndDate)
--;
----===== Create "random constrained whole dates" within
-- -- the parameters identified in the variables above.
-- SELECT TOP (@NumberOfRows)
-- SomeRandomDate1 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate,
-- SomeRandomDate2 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate,
-- SomeRandomDate3 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate,
-- SomeRandomDate4 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate
--INTO #Test
-- FROM sys.all_columns ac1
-- CROSS JOIN sys.all_columns ac2
--;
PRINT 'New Test'
DECLARE @max-2datetime,
@min-2datetime
DECLARE @Date datetime2
SET @Date = SYSDATETIME()
SELECT @max-2 = CASE WHEN SomeRandomDate1 >= SomeRandomDate2
AND SomeRandomDate1 >= SomeRandomDate3
AND SomeRandomDate1 >= SomeRandomDate4 THEN SomeRandomDate1
WHEN SomeRandomDate2 >= SomeRandomDate3
AND SomeRandomDate2 >= SomeRandomDate4 THEN SomeRandomDate2
WHEN SomeRandomDate3 >= SomeRandomDate4 THEN SomeRandomDate3 ELSE SomeRandomDate4 END,
@min-2 = CASE WHEN SomeRandomDate1 <= SomeRandomDate2
AND SomeRandomDate1 <= SomeRandomDate3
AND SomeRandomDate1 <= SomeRandomDate4 THEN SomeRandomDate1
WHEN SomeRandomDate2 <= SomeRandomDate3
AND SomeRandomDate2 <= SomeRandomDate4 THEN SomeRandomDate2
WHEN SomeRandomDate3 <= SomeRandomDate4 THEN SomeRandomDate3 ELSE SomeRandomDate4 END
FROM #Test
PRINT DATEDIFF( ms, @Date, SYSDATETIME())
SET @Date = SYSDATETIME()
SELECT @max-2 = HiDate, @min-2 = LoDate
FROM #Test t
CROSS APPLY dbo.HiLoDate(SomeRandomDate1, SomeRandomDate2,SomeRandomDate3,SomeRandomDate4) HiLoDate
PRINT DATEDIFF( ms, @Date, SYSDATETIME())
SET @Date = SYSDATETIME()
SELECT @max-2 = HiDate, @min-2 = LoDate
FROM #Test t
CROSS APPLY dbo.HiLoDate2(SomeRandomDate1, SomeRandomDate2,SomeRandomDate3,SomeRandomDate4) HiLoDate
PRINT DATEDIFF( ms, @Date, SYSDATETIME())
GO 5
And the results where:
New Test
457 --Direct comparison
606 --HiLoDate
547 --HiLoDate2
New Test
459 --Direct comparison
584 --HiLoDate
540 --HiLoDate2
New Test
466 --Direct comparison
592 --HiLoDate
539 --HiLoDate2
New Test
453 --Direct comparison
590 --HiLoDate
555 --HiLoDate2
New Test
459 --Direct comparison
585 --HiLoDate
545 --HiLoDate2
What would you choose? simplicity or 0.1 seconds of performance over 1million rows?