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, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
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, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
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 @ 7:56 PM
Points: 36,775, Visits: 31,230
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
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 @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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 @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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