GReatest Value - there has to be a better way?

Hi all,

I've just gotten a new request, and I can't see a better way (unless there is a BIGGEST, GREATEST function I don't know of) to select the colun that has the largest value, from 4 columns in a row, other than what I've just hacked together below. The thinking is to then make a function out of this ugliness, and have that available. Naturally, I am hoping someone can either smack me upside the head with a BOL reference I can't find, or provide the village idiot with a better way of doing it.



declare@col1datetime,
@col2datetime,
@col3datetime,
@col4datetime

set @col1 = getdate()
set @col2 = dateadd(day,-1,getdate())
set @col3 = dateadd(day,1,getdate())
set @col4 = dateadd(day,-3,getdate())

select @col1,@col2,@col3,@Col4,
case
when @col1 > @col2
then
casewhen @col1 > @col3
then
casewhen @col1 > @Col4
then @col1
else @col4
end
else casewhen @col3 > @col4
then @col3
else @col4
end
end
elsecasewhen @col2 > @col3
then
casewhen @col2 > @col4
then @col2
else @col4
end
elsecasewhen @col3 > @col4
then @col3
else @col4
end
end
end as 'Greatest Value'


Help?[:0]

Panic, Chaos, Disorder ... my work here is done --unknown

I've now got this one - seems about the nicest (i think - what do you think?), since it is the easiest to extend:



create function dbo.MaxDate (@col1 datetime,@col2 datetime,@col3 datetime,@col4 datetime)
returns datetime
as
begin
declare @Maxdate datetime
declare @SortTable table (datecol datetime)
insert into @SortTable (datecol) values (@col1)
insert into @SortTable (datecol) values (@col2)
insert into @SortTable (datecol) values (@col3)
insert into @SortTable (datecol) values (@col4)

select @Maxdate = max (datecol) from @SortTable
return @MaxDate
end


But your's is certainly nicer than my original one, thanks.

Panic, Chaos, Disorder ... my work here is done --unknown

Frank,<br /><br />As I read it, your's doesn't actually meet my unspecified requirement ... upspecified requirement - I guess we are all used to those [<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br />I need the maximum date per row, and not for the entire table - I will actually be returning a resultset along the lines (pseduo code) of:<br /><br />name,surname,maxdate(DateOfApplication,DateOfQuote,..)<br />for all clients...<br /><br />Of coourse, I'm a bit worried about performance at the moment...<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Frank,<br /><br />I do agree - the row by row is not nice. Fortunately, we've made a business call to decide that the DateOfApplication is the most relevant, so I no longer need to worry about this...<br /><br />Regards the 1nf point ... while I can (and will [<img src='/community/emoticons/emotion-1.gif' alt='' />] ) plead the "legacy database" arguement, what we were looking at, before we convinced business, was a requirement to display the most recent datetime of a row for an application which has: ApplicationDateTime, OfferDateTime, AcceptanceDateTime, CompletionDateTime.<br /><br />I'm not certain that going from:<br /><br />Application<br />-----------<br />ID,<br />ApplicationType,<br />ApplicationDateTime,<br />OfferDateTime,<br />AcceptanceDateTime,<br />CompletionDateTime<br /><br />to something like:<br /><pre id="code"><font face="courier" size="2" id="code"><br />Application-----------------&lt;ApplicationEvent&gt;----------ApplicationEventType<br />----------- ---------------- --------------------<br />ID, ID, No,<br />ApplicationType ApplicationEventType_No, Name,<br /> DateTime Description<br /><br />...<br /> Acceptance, <br /> Offer,<br /> Completion,<br /> Application<br /><br /></font id="code"></pre id="code"><br />Is really the best way? Do you remove DateOFBirth and MarraigeDate from a party row just because they could be handled more generically in the 2nd model... I wouldn't...<br /><br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Hmm - provoked some interest, it seems [:0]<br /><br />Some feedback/comments...<br /><br />Now, another interesting bit. Due to all the responses, I decided to go and test this all in my test environment anyway. The results where somewhat interesting:<br /><br />NOTE - Frank, I had some trouble converting your's to work in line, but since I was going quickly, and largely Find&Replacing, that may be on my side...<br /><br />The 2 functions created:<br /><pre id="code"><font face="courier" size="2" id="code"><br />drop function dbo.GreatestDate<br />go<br />CREATE FUNCTION dbo.GreatestDate (<br /> @col1 datetime,<br /> @col2 datetime,<br /> @col3 datetime,<br /> @col4 datetime)<br />returns Datetime<br />as <br />begin<br />declare @GreatestDate Datetime<br />select --@col1,@col2,@col3,@Col4,<br /> @GreatestDate =<br /> case<br /> when @col1 &gt; @col2<br /> then<br /> case when @col1 &gt; @col3<br /> then<br /> case when @col1 &gt; @Col4<br /> then @col1<br /> else @col4<br /> end<br /> else case when @col3 &gt; @col4<br /> then @col3<br /> else @col4<br /> end<br /> end<br /> else case when @col2 &gt; @col3<br /> then<br /> case when @col2 &gt; @col4<br /> then @col2<br /> else @col4<br /> end<br /> else case when @col3 &gt; @col4<br /> then @col3<br /> else @col4<br /> end<br /> end<br /> end <br />return @GreatestDate <br />end <br />go <br /><br />drop function dbo.MaxDate<br />go<br />create function dbo.MaxDate (@col1 datetime,@col2 datetime,@col3 datetime,@col4 datetime)<br />returns datetime<br />as<br />begin<br />declare @Maxdate datetime<br />declare @SortTable table (datecol datetime)<br />insert into @SortTable (datecol) values (@col1)<br />insert into @SortTable (datecol) values (@col2)<br />insert into @SortTable (datecol) values (@col3)<br />insert into @SortTable (datecol) values (@col4)<br /><br />select @Maxdate = max (datecol) from @SortTable<br />return @MaxDate<br />end <br />go <br /><br /></font id="code"></pre id="code"><br /><br />the variations run:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @StartTime AS DateTime <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt='' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ SET @StartTime = GetDate()<br />select <br />--regan<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion, <br />dbo.greatestdate(strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion) <br />as Orginial<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ PRINT '' PRINT '************************* Overall Duration: regan' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt='' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ SET @StartTime = GetDate()<br />select <br />--Mark<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion, <br />dbo.maxdate(strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion) <br />as Mark<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ PRINT '' PRINT '************************* Overall Duration: Mark' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt='' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ SET @StartTime = GetDate()<br />select <br />--MMarovic<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion,<br /> case<br /> when strDateTimeofApplication &gt;= strDateTimeofOffer <br />and strDateTimeofApplication &gt;= strDateTimeofAcceptance <br />and strDateTimeofApplication &gt;= strDateTimeofCompletion <br />then strDateTimeofApplication<br /> when strDateTimeofOffer &gt; strDateTimeofApplication <br />and strDateTimeofOffer &gt;= strDateTimeofAcceptance <br />and strDateTimeofOffer &gt;= strDateTimeofCompletion <br />then strDateTimeofOffer<br /> when strDateTimeofAcceptance &gt; strDateTimeofApplication <br />and strDateTimeofAcceptance &gt; strDateTimeofOffer <br />and strDateTimeofAcceptance &gt;= strDateTimeofCompletion <br />then strDateTimeofAcceptance<br /> else strDateTimeofCompletion<br /> end as MMarovic<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ PRINT '' PRINT '************************* Overall Duration: MMarovic ' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br />/* ki:NOCOUNT */ SET NOCOUNT ON<br />/* ki:CHECKPOINT */ CHECKPOINT<br />/* ki:CLEANBUFFER */ DBCC DROPCLEANBUFFERS<br />/* ki<img src='/community/emoticons/emotion-4.gif' alt='' />ROCCACHE */ DBCC FREEPROCCACHE<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ SET @StartTime = GetDate()<br />SELECT<br />--RockMoose<br /> strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion, --&lt;-------------- Let's get the maximum of these 4 columns<br /> (<br /> SELECT MAX(val) FROM<br /> ( SELECT strDateTimeofApplication AS val<br /> UNION SELECT strDateTimeofOffer<br /> UNION SELECT strDateTimeofAcceptance<br /> UNION SELECT strDateTimeofCompletion<br /> ) vals<br /> ) AS RockMoose<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/* ki<img src='/community/emoticons/emotion-2.gif' alt='' />URATION */ PRINT '' PRINT '************************* Overall Duration: RockMoose ' + Cast(DateDiff(ms, @StartTime, GetDate()) AS varchar(30)) + ' ms *************************' PRINT '' <br /></font id="code"></pre id="code"><br /><br /><br /><br /><br />The results are interesting:<br /><br />************************* Overall Duration: regan3436 ms *************************<br />************************* Overall Duration: Mark18140 ms *************************<br />************************* Overall Duration: MMarovic 3453 ms *************************<br />************************* Overall Duration: RockMoose 3403 ms *************************<br /><br />This was in returning resultsets of 12049 rows...<br /><br />Access Paths for 3 are exactly the same - the long one has a far different one...<br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

I have to add that I like RockMoose solution not just from performance point of view.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Btw, Regan. <s>Interesting name of a DB. </s><br />Interesting name for a server. [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Frank, you were right first time - it's the db name [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />Yes, also posted @SQLTeam - I generally do that if I think it might be a little challenging - I know there are several people that are on both forums (yourself, Derrick, and several others I've just discovered are on both [<img src='/community/emoticons/emotion-2.gif' alt='' />] ) - I hope that isn't a serious breach of forums-iquette [?]<br /><br />I'd be interested in your (and everyone else's) point of view on the normalization comments...<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />You know that a scalar UDF evaluates row-by-row? Not really nice. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I'm a bit surprised by my results. If you look, you'll notice that basically my 2 functions perform near-as-damnit to the same as RockMooses straight SQL. What happenened to the row-by-row, or is Rockmooses also doing row-by-row?<br /><br />Just to add a last wrinkle, I'll take my initial monster out of the functions, and throw it in as straight SQL, and then do my last set of testing - namely multi-iteration (I think I'll do 1000 executions for each statement) - and I'll post the results, fyi.<br /><br /><br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Please check execution plan also, I'm curious to see how exec plan of RockMoose looks like.

Will do ... had a hectic day, so that's on the list for tomorrow now [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

This got quite interesting (is that the geek in me coming out?).<br />I ran this several times, with up to 10 iterations, bring the data back to <br />the client. Because of the fact that in my environment, I get 12K rows per <br />query, that became a bit excessive (PC REALLY started grinding when I had <br />a 1.2 M row report [}<img src='/community/emoticons/emotion-1.gif' alt='' />], for testing over 10 times, I dumped data into <br />temp tables, and pushed iterations up to 100, and for the 100 runs, I <br />remove Mark's function, which consistently performs 6.5 to 10 times WORSE <br />than the other variations...<br /><br />MARK - if performance is an issue for you, read this - you may want to <br />review and see if these results are true for you.<br /><br />The only fixed conclusion's I can draw are:<br /><br />My code runs better as SQL than as a function (marignal on 1 execution, but getting better and better as # iterations goes up)<br />Marc's function really doesn't look like it performs near any of the other options;<br />Strangely, Frank's solution, which I thought would end up being the best, seems to scale slightly worse than some of the others.<br /><br />Anyone who is interested, and cares, maybe you can go through my testing, and results, and point out errors in my methodology, or conclusions...<br /><br />Results : 10 iterations, returned to client:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />*** Regan SQL : Iteration: 10 Duration: 720 ms ***<br />*** Regan SQL : Overall Duration: 9906 ms ***<br />*** Regan SQL : Average Duration: 990.60000000000<br /><br />*** Regan Function : Iteration: 10 Duration: 733 ms ***<br />*** Regan Function : Overall Duration: 10080 ms ***<br />*** Regan Function : Average Duration: 1008.00000000000<br /><br />*** Marc Function : Iteration: 10 Duration: 6250 ms ***<br />*** Marc Function : Overall Duration: 65220 ms ***<br />*** Marc Function : Average Duration: 6522.00000000000<br /><br />*** MMarovic SQL : Iteration: 10 Duration: 750 ms ***<br />*** MMarovic SQL : Overall Duration: 10063 ms ***<br />*** MMarovic SQL : Average Duration: 1006.30000000000<br /><br />*** Rockmoose SQL : Iteration: 10 Duration: 763 ms ***<br />*** Rockmoose SQL : Overall Duration: 10030 ms ***<br />*** Rockmoose SQL : Average Duration: 1003.00000000000<br /><br />*** Frank SQL : Iteration: 10 Duration: 560 ms ***<br />*** Frank SQL : Overall Duration: 8513 ms ***<br />*** Frank SQL : Average Duration: 851.30000000000<br /></font id="code"></pre id="code"><br /><br />Results : 100 iterations to temp table, only durations etc. report <br /><br />(excluded Marc's fuction)<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />*** Regan SQL : Overall Duration: 11016 ms ***<br />*** Regan SQL : Average Duration: 110.16000000000<br /><br />*** Regan Function : Overall Duration: 22733 ms ***<br />*** Regan Function : Average Duration: 227.33000000000<br /><br />*** MMarovic SQL : Overall Duration: 10936 ms ***<br />*** MMarovic SQL : Average Duration: 109.36000000000<br /><br />*** Rockmoose SQL : Overall Duration: 24173 ms ***<br />*** Rockmoose SQL : Average Duration: 241.73000000000<br /><br />*** Frank SQL : Overall Duration: 18733 ms ***<br />*** Frank SQL : Average Duration: 187.33000000000<br /><br /></font id="code"></pre id="code"><br /><br />Intersting that in the return to client, Franks seemed to be winning, but <br />not in the temp table version - then I realized that Franks was only <br />returning 1 date and an ID column, whereas my previous tests had all <br />others 5 dates ... is that the difference ? I'm not sure...<br /><br />Just for the heck of it, I ran a set of 1000 iterations. It seems the results differ a bit. Having said that, given that this would have create 5 temp tables, each populated with 12 Million rows, maybe tempdb usage impacted.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />*** Regan SQL : Overall Duration: 96280 ms ***<br />*** Regan SQL : Average Duration: 96.28000000000<br /><br />*** Regan Function : Overall Duration: 198490 ms ***<br />*** Regan Function : Average Duration: 198.49000000000<br /><br />*** MMarovic SQL : Overall Duration: 81673 ms ***<br />*** MMarovic SQL : Average Duration: 81.67300000000<br /><br />*** Rockmoose SQL : Overall Duration: 213113 ms ***<br />*** Rockmoose SQL : Average Duration: 213.11300000000<br /><br />*** Frank SQL : Overall Duration: 159953 ms ***<br />*** Frank SQL : Average Duration: 159.95300000000<br /><br /></font id="code"></pre id="code"><br /><br />The code I ran for the insert iterations:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @StartTime AS DateTime <br />DECLARE @LoopMax AS INT, @IterationNumber AS INT, @IterationTime AS <br /><br />DateTime <br />DECLARE @Optionvarchar(25)<br /><br />SET NOCOUNT ON<br />SET @LoopMax = 100<br /><br />CHECKPOINT /* No unnecessary IO's when writing to log */<br />DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */<br />DBCC FREEPROCCACHE /* Clean out execution plans in cache */<br /><br />/********************** Initialise DURATION Loop ***********************/<br />SET @StartTime = GetDate()<br />SET @IterationNumber = 1 <br />SET @Option = 'Regan SQL'<br />create table #TempResults1 (dt1datetime)<br />WHILE @IterationNumber &lt;= @LoopMax <br />BEGIN <br />SET @IterationTime = GetDate()<br />insert into #TempResults1 (dt1)<br />select <br />case<br />when strDateTimeofApplication &gt; strDateTimeofOffer <br /><br />then<br />case when strDateTimeofApplication &gt; <br /><br />strDateTimeofAcceptance then<br />case when strDateTimeofApplication <br /><br />&gt; strDateTimeofCompletion then strDateTimeofApplication<br />else strDateTimeofCompletion<br />end<br />else <br />case when strDateTimeofAcceptance <br /><br />&gt; strDateTimeofCompletion then strDateTimeofAcceptance<br />else strDateTimeofCompletion<br />end<br />end<br />else <br />case when strDateTimeofOffer &gt; <br /><br />strDateTimeofAcceptance then<br />case when strDateTimeofOffer &gt; <br /><br />strDateTimeofCompletion then strDateTimeofOffer<br />else strDateTimeofCompletion<br />end<br />else <br />case when strDateTimeofAcceptance <br /><br />&gt; strDateTimeofCompletion then strDateTimeofAcceptance<br />else strDateTimeofCompletion<br />end<br />end<br />end as GreatestDate<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br /><br />/****************** End Stmnt & Close DURATION Loop <br /><br />**********************/<br />-- PRINT '*** '+@option+' : Iteration: '+Cast(@IterationNumber AS <br /><br />varchar(10))+' Duration: '<br />-- +Cast(DateDiff(ms, @IterationTime, GetDate()) AS <br /><br />varchar(30)) + ' ms ***' <br />SET @IterationNumber = @IterationNumber + 1 <br />END<br />drop table #TempResults1<br />/************************** End Statement <br /><br />********************************/<br />PRINT '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, <br /><br />GetDate()) AS varchar(30))<br />+' ms ***' <br />select '*** '+@option+' : Average Duration: ',Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS decimal)/@Loopmax<br /><br />CHECKPOINT /* No unnecessary IO's when writing to log */<br />DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */<br />DBCC FREEPROCCACHE /* Clean out execution plans in cache */<br /><br />/********************** Initialise DURATION Loop ***********************/<br />SET @StartTime = GetDate()<br />SET @IterationNumber = 1 <br />SET @Option = 'Regan Function'<br />create table #TempResults2 (dt1datetime)<br />WHILE @IterationNumber &lt;= @LoopMax <br />BEGIN <br />SET @IterationTime = GetDate()<br />insert into #TempResults2 (dt1)<br />select <br />--Regan Function<br />dbo.greatestdate(<br />strDateTimeofApplication,<br />strDateTimeofOffer,<br />strDateTimeofAcceptance,<br />strDateTimeofCompletion) <br />as Orginial<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/****************** End Stmnt & Close DURATION Loop <br /><br />**********************/<br />-- PRINT '*** '+@option+' : Iteration: '+Cast(@IterationNumber AS <br /><br />varchar(10))+' Duration: '<br />-- +Cast(DateDiff(ms, @IterationTime, GetDate()) AS <br /><br />varchar(30)) + ' ms ***' <br />SET @IterationNumber = @IterationNumber + 1 <br />END<br />drop table #TempResults2<br />/************************** End Statement <br /><br />********************************/<br />PRINT '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, <br /><br />GetDate()) AS varchar(30))<br />+' ms ***' <br />select '*** '+@option+' : Average Duration: ',Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS decimal)/@Loopmax<br /><br />-- CHECKPOINT /* No unnecessary IO's when writing to log */<br />-- DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */<br />-- DBCC FREEPROCCACHE /* Clean out execution plans in cache */<br />-- <br />-- /********************** Initialise DURATION Loop <br /><br />***********************/<br />-- SET @StartTime = GetDate()<br />-- SET @IterationNumber = 1 <br />-- SET @Option = 'Marc Function'<br />-- create table #TempResults3 (dt1datetime)<br />-- WHILE @IterationNumber &lt;= @LoopMax <br />-- BEGIN <br />-- SET @IterationTime = GetDate()<br />-- insert into #TempResults3 (dt1)<br />-- select <br />-- --Mark Function<br />-- dbo.maxdate(<br />-- strDateTimeofApplication,<br />-- strDateTimeofOffer,<br />-- strDateTimeofAcceptance,<br />-- strDateTimeofCompletion) <br />-- as Mark<br />-- fromRedbaron.dbo.Application<br />-- whereProduct_Id is null<br />-- /****************** End Stmnt & Close DURATION Loop <br /><br />**********************/<br />-- PRINT '*** '+@option+' : Iteration: '+Cast(@IterationNumber AS <br /><br />varchar(10))+' Duration: '<br />-- +Cast(DateDiff(ms, @IterationTime, GetDate()) AS <br /><br />varchar(30)) + ' ms ***' <br />-- SET @IterationNumber = @IterationNumber + 1 <br />-- END<br />-- drop table #TempResults3<br />-- /************************** End Statement <br /><br />********************************/<br />-- PRINT '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS varchar(30))<br />-- +' ms ***' <br />-- select '*** '+@option+' : Average Duration: ',Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS decimal)/@Loopmax<br /><br />CHECKPOINT /* No unnecessary IO's when writing to log */<br />DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */<br />DBCC FREEPROCCACHE /* Clean out execution plans in cache */<br /><br />/********************** Initialise DURATION Loop ***********************/<br />SET @StartTime = GetDate()<br />SET @IterationNumber = 1 <br />SET @Option = 'MMarovic SQL'<br />create table #TempResults4 (dt1datetime)<br />WHILE @IterationNumber &lt;= @LoopMax <br />BEGIN <br />SET @IterationTime = GetDate()<br />insert into #TempResults4 (dt1)<br />select <br />--MMarovic SQL<br /> case<br /> when strDateTimeofApplication &gt;= <br /><br />strDateTimeofOffer <br />and strDateTimeofApplication &gt;= <br /><br />strDateTimeofAcceptance <br />and strDateTimeofApplication &gt;= <br /><br />strDateTimeofCompletion <br />then strDateTimeofApplication<br /> when strDateTimeofOffer &gt; <br /><br />strDateTimeofApplication <br />and strDateTimeofOffer &gt;= <br /><br />strDateTimeofAcceptance <br />and strDateTimeofOffer &gt;= <br /><br />strDateTimeofCompletion <br />then strDateTimeofOffer<br /> when strDateTimeofAcceptance &gt; <br /><br />strDateTimeofApplication <br />and strDateTimeofAcceptance &gt; <br /><br />strDateTimeofOffer <br />and strDateTimeofAcceptance &gt;= <br /><br />strDateTimeofCompletion <br />then strDateTimeofAcceptance<br /> else strDateTimeofCompletion<br /> end as MMarovic<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/****************** End Stmnt & Close DURATION Loop <br /><br />**********************/<br />-- PRINT '*** '+@option+' : Iteration: '+Cast(@IterationNumber AS <br /><br />varchar(10))+' Duration: '<br />-- +Cast(DateDiff(ms, @IterationTime, GetDate()) AS <br /><br />varchar(30)) + ' ms ***' <br />SET @IterationNumber = @IterationNumber + 1 <br />END<br />drop table #TempResults4<br />/************************** End Statement <br /><br />********************************/<br />PRINT '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, <br /><br />GetDate()) AS varchar(30))<br />+' ms ***' <br />select '*** '+@option+' : Average Duration: ',Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS decimal)/@Loopmax<br /><br />CHECKPOINT /* No unnecessary IO's when writing to log */<br />DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */<br />DBCC FREEPROCCACHE /* Clean out execution plans in cache */<br /><br />/********************** Initialise DURATION Loop ***********************/<br />SET @StartTime = GetDate()<br />SET @IterationNumber = 1 <br />SET @Option = 'Rockmoose SQL'<br />create table #TempResults5 (dt1datetime)<br />WHILE @IterationNumber &lt;= @LoopMax <br />BEGIN <br />SET @IterationTime = GetDate()<br />insert into #TempResults5 (dt1)<br />SELECT<br />--RockMoose<br /> (SELECT MAX(val) FROM<br /> ( SELECT strDateTimeofApplication AS val<br /> UNION SELECT strDateTimeofOffer<br /> UNION SELECT strDateTimeofAcceptance<br /> UNION SELECT strDateTimeofCompletion<br /> ) vals<br /> ) AS RockMoose<br />fromRedbaron.dbo.Application<br />whereProduct_Id is null<br />/****************** End Stmnt & Close DURATION Loop <br /><br />**********************/<br />-- PRINT '*** '+@option+' : Iteration: '+Cast(@IterationNumber AS <br /><br />varchar(10))+' Duration: '<br />-- +Cast(DateDiff(ms, @IterationTime, GetDate()) AS <br /><br />varchar(30)) + ' ms ***' <br />SET @IterationNumber = @IterationNumber + 1 <br />END<br />drop table #TempResults5<br />/************************** End Statement <br /><br />********************************/<br />PRINT '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, <br /><br />GetDate()) AS varchar(30))<br />+' ms ***' <br />select '*** '+@option+' : Average Duration: ',Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS decimal)/@Loopmax<br /><br />CHECKPOINT /* No unnecessary IO's when writing to log */<br />DBCC DROPCLEANBUFFERS /* Clean out data pages in buffer */<br />DBCC FREEPROCCACHE /* Clean out execution plans in cache */<br /><br />/********************** Initialise DURATION Loop ***********************/<br />SET @StartTime = GetDate()<br />SET @IterationNumber = 1 <br />SET @Option = 'Frank SQL'<br />create table #TempResults6 (dt1datetime)<br />WHILE @IterationNumber &lt;= @LoopMax <br />BEGIN <br />SET @IterationTime = GetDate()<br />insert into #TempResults6 (dt1)<br />SELECT<br />--Frank SQL<br />MAX(iDate) AS maxdate<br />FROM <br />(SELECT <br />id, <br />CASE i <br />WHEN 1 THEN StrDatetimeOfApplication <br />WHEN 2 THEN strDateTimeofOffer<br />WHEN 3 THEN strDateTimeofAcceptance<br />WHEN 4 THEN strDateTimeofCompletion<br />END AS IDate <br />FROM <br />Redbaron..application <br />CROSS JOIN <br />(SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT <br /><br />3 UNION ALL SELECT 4 ) NumberColumnsTable<br />WHERE Product_ID is null<br />) XJoinResultTable<br />GROUP BY id<br />/****************** End Stmnt & Close DURATION Loop <br /><br />**********************/<br />-- PRINT '*** '+@option+' : Iteration: '+Cast(@IterationNumber AS <br /><br />varchar(10))+' Duration: '<br />-- +Cast(DateDiff(ms, @IterationTime, GetDate()) AS <br /><br />varchar(30)) + ' ms ***' <br />SET @IterationNumber = @IterationNumber + 1 <br />END<br />drop table #TempResults6<br />/************************** End Statement <br /><br />********************************/<br />PRINT '*** '+@option+' : Overall Duration: '+Cast(DateDiff(ms, @StartTime, <br /><br />GetDate()) AS varchar(30))<br />+' ms ***' <br />select '*** '+@option+' : Average Duration: ',Cast(DateDiff(ms, <br /><br />@StartTime, GetDate()) AS decimal)/@Loopmax<br /><br />/* NOCOUNT */ SET NOCOUNT OFF<br />/* IO */ SET STATISTICS IO OFF <br />/* CPU & TIME */ SET STATISTICS TIME OFF<br />/* PROFILE */ SET STATISTICS PROFILE OFF<br />/****************************** DONE <br /><br />***************************************/<br /></font id="code"></pre id="code"><br /><br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Unfotunatelly right now I don't have time for testing (project deadline next Thursday), so I hope someone will have time to test rockMoose modification of my solution:
case
when strDateTimeofApplication > strDateTimeofOffer and
strDateTimeofApplication > strDateTimeofAcceptance and
strDateTimeofApplication > strDateTimeofCompletion
then strDateTimeofApplication
when strDateTimeofOffer > strDateTimeofAcceptance and
strDateTimeofOffer > strDateTimeofCompletion
then strDateTimeofOffer
when strDateTimeofAcceptance > strDateTimeofCompletion
then strDateTimeofAcceptance
else strDateTimeofCompletion
end

Quite happy with that Frank...

I'll get you some testing done on the last option, and if you want, I'll give you info regards environments run, etc.

Panic, Chaos, Disorder ... my work here is done --unknown

Go ahead and let us know when it's done. [<img src='/community/emoticons/emotion-1.gif' alt='' />]

Collated and completed results for all version (Marc's function removed from 100 and 1000 runtimes due to slow speed. Is would seem that mmarovic #2 variations wins out over the long term:




10- iterations
*** mmarovic V2 (rockmoose variation) SQL : Overall Duration: 7406 ms ***
*** mmarovic V2 (rockmoose variation) SQL : Average Duration: 740.60000000000

*** Frank SQL : Overall Duration: 8513 ms ***
*** Frank SQL : Average Duration: 851.30000000000

*** Regan SQL : Overall Duration: 9906 ms ***
*** Regan SQL : Average Duration: 990.60000000000

*** Rockmoose SQL : Overall Duration: 10030 ms ***
*** Rockmoose SQL : Average Duration: 1003.00000000000

*** MMarovic SQL : Overall Duration: 10063 ms ***
*** MMarovic SQL : Average Duration: 1006.30000000000

*** Regan Function : Overall Duration: 10080 ms ***
*** Regan Function : Average Duration: 1008.00000000000

*** Marc Function : Overall Duration: 65220 ms ***
*** Marc Function : Average Duration: 6522.00000000000


100- iterations
*** mmarovic V2 (rockmoose variation) SQL : Overall Duration: 10533 ms ***
*** mmarovic V2 (rockmoose variation) SQL : Average Duration: 105.33000000000

*** MMarovic SQL : Overall Duration: 10936 ms ***
*** MMarovic SQL : Average Duration: 109.36000000000

*** Regan SQL : Overall Duration: 11016 ms ***
*** Regan SQL : Average Duration: 110.16000000000

*** Frank SQL : Overall Duration: 18733 ms ***
*** Frank SQL : Average Duration: 187.33000000000

*** Regan Function : Overall Duration: 22733 ms ***
*** Regan Function : Average Duration: 227.33000000000

*** Rockmoose SQL : Overall Duration: 24173 ms ***
*** Rockmoose SQL : Average Duration: 241.73000000000

1000- iterations

*** mmarovic V2 (rockmoose variation) SQL : Overall Duration: 75640 ms ***
*** mmarovic V2 (rockmoose variation) SQL : Average Duration: 75.64000000000

*** MMarovic SQL : Overall Duration: 81673 ms ***
*** MMarovic SQL : Average Duration: 81.67300000000

*** Regan SQL : Overall Duration: 96280 ms ***
*** Regan SQL : Average Duration: 96.28000000000

*** Frank SQL : Overall Duration: 159953 ms ***
*** Frank SQL : Average Duration: 159.95300000000

*** Regan Function : Overall Duration: 198490 ms ***
*** Regan Function : Average Duration: 198.49000000000

*** Rockmoose SQL : Overall Duration: 213113 ms ***
*** Rockmoose SQL : Average Duration: 213.11300000000




Panic, Chaos, Disorder ... my work here is done --unknown

Out of interest do you get a difference with Rockmoose's if you use UNION ALL instead of UNION. For a very basic test here I get a different execution plan for UNION ALL. Does it actually make any difference?

Regards,

Robert.

PS Also how does UDF using the same method compare to Regan's function?

It's not done yet. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />It looks like that droping down to the next when clause of case statement is more costly then testing one more condition inside when clause.<br /><br />RockMouse variation has the structure:<br /><br />case<br /> when col1 &gt; col2 and col1 &gt; col3 and col1 &gt; col4 then col1<br /> when col2 &gt; col3 and col2 &gt; col4 then col2<br /> when col3 &gt; col4 then col3<br /> else col4<br />end<br /><br />Now if we choose col1 to be one that in most cases contains the biggest value, col2 next one based on the same criteria and so on, we can make even the faster query (not too much unless distibution is extremely unbalanced and optimal combination is different then one we tested).<br /><br />Off course that will be true only in case my theory is correct.

You are right, we will not gain anything if distribution is fairly balanced and even if it is not, we will just slightly improve performance. However I would like it to be tested to confirm or prove wrong the theory mentioned.

Actually, the distribution is not even. Basically, the ApplicationDateTime will always be populated, and will always be the oldest UNLESS the others are default '1900-01-01', since the design way back when decided to default '1900-01-01' rather than use NULLs.

If all have valid values (i.e. not = '1900-01-01') then Completed will be greater than Accepted will be greater than Offered will be greater than Application.

In terms of spread, for Applications that have NULL Product_ID (i.e no product has yet bee created), I will check Thursday (away for 2 days on a process/methodology brainstorm and workshop) what the distribution is. As I mentioned somewhere along the thread, it was discovered that we didn't actually have to do this calculation, so since then, it has been an intellectual exercise. I'll get those distribution numbers so that we can try and generate these best query.

Frank, I've never really written an article before. If you think it is worthwehile, I'll try and do one, and then bounce it off you, if your willing...

CiaO 4 NoW

Panic, Chaos, Disorder ... my work here is done --unknown