Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Is there a Coalesce LIke function that returns the lowest/highest value of a set of params passed to it Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 2:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
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!
Post #1376239
Posted Tuesday, October 23, 2012 2:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1376241
Posted Tuesday, October 23, 2012 2:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
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!
Post #1376247
Posted Tuesday, October 23, 2012 2:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1376249
Posted Tuesday, October 23, 2012 2:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1376251
Posted Tuesday, October 23, 2012 2:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1376253
Posted Tuesday, October 23, 2012 3:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 3,783, Visits: 8,485
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 datetime,
@MIN datetime
DECLARE @Date datetime2

SET @Date = SYSDATETIME()
SELECT @MAX = 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 = 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 = HiDate, @MIN = LoDate
FROM #Test t
CROSS APPLY dbo.HiLoDate(SomeRandomDate1, SomeRandomDate2,SomeRandomDate3,SomeRandomDate4) HiLoDate

PRINT DATEDIFF( ms, @Date, SYSDATETIME())
SET @Date = SYSDATETIME()

SELECT @MAX = HiDate, @MIN = 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?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1376265
Posted Tuesday, October 23, 2012 4:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
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!
Post #1376275
Posted Tuesday, October 23, 2012 4:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
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!
Post #1376276
Posted Tuesday, October 23, 2012 4:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 3,783, Visits: 8,485
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.
http://www.sqlservercentral.com/articles/Test+Data/88964/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1376280
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse