SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a Coalesce LIke function that returns the lowest/highest value of a set of params passed to...


Is there a Coalesce LIke function that returns the lowest/highest value of a set of params passed to it

Author
Message
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 1665
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!
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5404 Visits: 6900
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
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 1665
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!
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5404 Visits: 6900
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86125 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5404 Visits: 6900
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16563 Visits: 19098
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 1665
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!
YSLGuru
YSLGuru
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 1665
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!
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16563 Visits: 19098
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search