October 23, 2012 at 2:20 pm
Coalesce() returns the first non-null value passed to it.
Does T-SQL have anything like this but that does this for numeric values where of a set of values passed it will return the highets or lowest value?
I have a set of data with 4 date/Time columns and I'd like to return the one with the hisghet value between the 4.
Thoughts?
Thanks
Kindest Regards,
Just say No to Facebook!October 23, 2012 at 2:25 pm
Here you go.
------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.HiLoDate
(
@date1 datetime,
@date2 datetime,
@date3 datetime,
@date4 datetime
)
RETURNS TABLE
AS
RETURN
(
with cte (xDate) as (
select @date1
union all
select @date2
union all
select @date3
union all
select @date4
)
select min(xDate) as LoDate, max(xDate) as HiDate
from
)
GO
/* TEST
select *
from dbo.HiLoDate('3/1/2012','3/15/2011','2/1/2012','11/1/2011')
*/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 23, 2012 at 2:49 pm
Thanks for the custom option Dixie but do you know if there is any native functinality in T-SQL that does this? What about in T-SQL 2012 if not in 2008?
Kindest Regards,
Just say No to Facebook!October 23, 2012 at 2:55 pm
And V2 is even better
CREATE FUNCTION [dbo].[HiLoDate2]
(
@date1 datetime,
@date2 datetime,
@date3 datetime,
@date4 datetime
)
RETURNS TABLE
AS
RETURN
(
with cte (max1,max2, min1,min2 ) as
(select case when @date1 >= @date2 then @date1 else @date2 end
,case when @date3 >= @date4 then @date3 else @date4 end
,case when @date1 <= @date2 then @date1 else @date2 end
,case when @date3 <= @date4 then @date3 else @date4 end
)
select case when min1 <= min2 then min1 else min2 end as LoDate
,case when max1 >= max2 then max1 else max2 end as HiDate
from cte
)
GO
/* TEST -- compare executions plans
select *
from dbo.HiLoDate('3/1/2012','3/15/2011','2/1/2012','11/1/2011')
select *
from dbo.HiLoDate2('3/1/2012','3/15/2011','2/1/2012','11/1/2011')
*/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 23, 2012 at 2:57 pm
YSLGuru (10/23/2012)
Thanks for the custom option Dixie but do you know if there is any native functinality in T-SQL that does this? What about in T-SQL 2012 if not in 2008?
This is currently no native function for such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2012 at 2:57 pm
Sorry... I overwrote my earlier comment that I know of no such native functionality.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 23, 2012 at 3:29 pm
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?
October 23, 2012 at 4:19 pm
Jeff Moden (10/23/2012)
YSLGuru (10/23/2012)
Thanks for the custom option Dixie but do you know if there is any native functinality in T-SQL that does this? What about in T-SQL 2012 if not in 2008?This is currently no native function for such a thing.
OK thanks
Kindest Regards,
Just say No to Facebook!October 23, 2012 at 4:20 pm
What tips form Jeff? I just see one poast saying there is no such native funcationailty. did I miss a post?
Kindest Regards,
Just say No to Facebook!October 23, 2012 at 4:38 pm
YSLGuru (10/23/2012)
What tips form Jeff? I just see one poast saying there is no such native funcationailty. did I miss a post?
No, I was referring to his article on how to create sample data.
October 23, 2012 at 7:11 pm
Luis Cazares (10/23/2012)
What would you choose? simplicity or 0.1 seconds of performance over 1million rows?
Like "Yogi" used to say, "When you come to a fork in the road, take it!" 😛
I chose both the performance and the simplicity by taking Luis' very fast direct method, putting it into an iTVF, and then calling it like this (display taken out of the picture using target variables like Luis did previously)...
DECLARE @MinDate DATETIME,
@MaxDate DATETIME
;
SELECT @MinDate = ca.MinDate,
@MaxDate = ca.MaxDate
FROM #Test t
CROSS APPLY dbo.FindMinMaxDate4(SomeRandomDate1, SomeRandomDate2, SomeRandomDate3, SomeRandomDate4) ca;
Here's Luis' code wrapped up in a nice clean iTVF.
CREATE FUNCTION dbo.FindMinMaxDate4
(
@pDateTime1 DATETIME,
@pDateTime2 DATETIME,
@pDateTime3 DATETIME,
@pDateTime4 DATETIME
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT MinDate =
CASE
WHEN @pDateTime1 <= @pDateTime2
AND @pDateTime1 <= @pDateTime3
AND @pDateTime1 <= @pDateTime4 THEN @pDateTime1
WHEN @pDateTime2 <= @pDateTime3
AND @pDateTime2 <= @pDateTime4 THEN @pDateTime2
WHEN @pDateTime3 <= @pDateTime4 THEN @pDateTime3
ELSE @pDateTime4
END,
MaxDate =
CASE
WHEN @pDateTime1 >= @pDateTime2
AND @pDateTime1 >= @pDateTime3
AND @pDateTime1 >= @pDateTime4 THEN @pDateTime1
WHEN @pDateTime2 >= @pDateTime3
AND @pDateTime2 >= @pDateTime4 THEN @pDateTime2
WHEN @pDateTime3 >= @pDateTime4 THEN @pDateTime3
ELSE @pDateTime4
END
;
{EDIT} p.s. I'm pretty sure that the presence of any NULL dates are going to throw a wrench into the gears.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2012 at 8:41 pm
YSLGuru, this is the direct comparison code from Luis' time trial. Similar to the V2 logic and apparently more efficient, which you would expect because only two case expressions have to be evaluated. ( One day, Jeff, one day.... )
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
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 23, 2012 at 8:44 pm
p.s. I'm pretty sure that the presence of any NULL dates are going to throw a wrench into the gears.
Agreed. One fix would be to add COALESCE or ISNULL expressions to work with the date parameters in order to force NULLS to high dates when calculating minimums, and low when calculating maximums.
P.S. Thanks for running the time trials, Luis. Would you run it again on your machine using Jeff's function? I wouldn't expect any overhead from CROSS APPLYing an itvf, but it would still be nice to see the final results. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 24, 2012 at 2:09 am
There is another way, I'm not sure about it's performance, but it takes less lines :hehe::
CREATE FUNCTION dbo.FindMinMaxDate4 (@v1 DATETIME, @v2 DATETIME, @v3 DATETIME, @v4 DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT MAX(v) AS dt_MAX, MIN(v) AS dt_MIN
FROM (VALUES(@v1),(@v2),(@v3),(@v4)) v(v)
October 24, 2012 at 5:45 am
The Dixie Flatline (10/23/2012)
YSLGuru, this is the direct comparison code from Luis' time trial.
Which I believe I clearly stated. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply