Need help with conditional logic in a case statement

I need to set the MinDate column to the smallest date of other date columns in the same record/row.  Note that an expert recommended to me that I use a case statement with an update statement. (See code marked EXPERT). That code is awesome, and I can get it to work if I have three date columns. Recently though I had to add two extra columns for LoggingDate and Expire date. I can't figure out how to change the case logic though to accommodate two more columns (LoggindDate and ExpireDate).
Here is my sql query which will work on three columns. Can someone show me how to update the case statement to find the minimum value based on 5 columns of dates? I am fairly good at if then logic, however incorporating it into a CASE statement is what I am having difficulty with.

(Expert sql solution below)
 UPDATE ClubList
 SET     MinDate =  
CASE WHEN RandDate >   ( CASE WHEN EntryDate  > CreationDate  THEN CreationDate  ELSE EntryDate END  )  
                                  THEN ( CASE WHEN EntryDate  > CreationDate  THEN CreationDate  ELSE EntryDate END   )
                                  ELSE  RandDate END

   
So here is how the data table will look before the MinDate is entered using sql. and below that is a list of how the table will look after the min date is entered into the MinDate column. (Also I scripted out the table and posted it as an attachement)

LastName     MinDate  EntryDate     CreationDate     RandDate               LoggingDate   ExpireDate
 Smiler          NULL     02/20/2010    01/22/2010       11/20/2010             01/22/1995    01/07/2010
 Greyson       NULL     11/07/2018    03/12/2010       01/20/2007           03/12/2010    01/20/2007
 Wolford       NULL     01/07/2010    01/07/2010       01/20/2015           04/20/2010    01/20/2010
 Lakeson       NULL     04/20/2010    01/20/2010       03/20/1998           01/20/2010    03/20/1998
 Chipper      NULL     02/01/1945    01/20/2010       07/26/2011           11/07/2011    07/26/2011  

 Here is the table after the min date is entered

 LastName  MinDate        EntryDate     CreationDate     RandDate         LoggingDate   ExpireDate
 Smiler        01/22/1995     02/20/2010    01/22/2010       11/20/2010      01/22/1995    01/07/2010
 Greyson     01/20/2007     11/07/2015    03/12/2010       01/20/2007      03/12/2010    01/20/2007
 Wolford     01/07/2010     01/07/2010    01/07/2010       01/20/2015      04/20/2010    01/20/2010
 Lakeson     03/20/1998     04/20/2010    01/20/2010       03/20/1998      01/20/2010    03/20/1998
 Chipper     02/01/1945     02/01/1945     01/20/2010       07/26/2011      11/07/2011    07/26/2011
Sql.txt
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
simplest way to solve this is to create the LEAST function in your db:
CREATE FUNCTION [dbo].[LeastDate] (@d1 datetime, @d2 datetime)
RETURNS datetime
AS
BEGIN
    DECLARE @least datetime

    IF @d1 is null or @d2 is null
        SET @least = null
    ELSE IF @d1 < @d2
        SET @least = @d1
    ELSE
        SET @least = @d2

    RETURN @least
END

Open in new window


and make your sql like this:
 UPDATE ClubList
 SET     MinDate =  dbo.LastDate( dbo.LeastDate( EntryDate, CreationDate) , RandDate )

Open in new window


and you can extend that easily to more columns if needed

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
Using cte and unpivoting will cover the circumstances no matter how many columns your are dealing.

With the current syntax you need to construct add the additional comparisons.

The first then has to be changed to another comparison  between two columns.  

I when you have three columns, you have two comparisons.

Compare column1 (( compare column2 and column 3) the result is compared to the result of ((compare column4 and column 5 ))
 UPDATE ClubList
 SET     MinDate =   
CASE WHEN RandDate >   ( Case when ( CASE WHEN EntryDate  > CreationDate  THEN CreationDate  ELSE EntryDate END  )   > ( CASE WHEN newdate > Otherdate THEN Otherdate ELSE Newdate END  ) 
                                  THEN ( CASE WHEN EntryDate  > CreationDate  THEN CreationDate  ELSE EntryDate END   )
                                  ELSE  RandDate END

Open in new window

Such that you have continually replicate/replace the case statements through out the structure.

I.e. The output of the outputting then will be a combinations of the case statements recalculating the same data set since while the tests are matched, the data ..........

It is a never ending nesting sets of case statements


1 2 3 4 5
If 5 > (if ( if 2> 3 )  then 3 else 2 )  >
           (If ( 4>1 ) then 1 else 4 ) then (If ( 4>1 ) then 1 else 4 ) else ( if 2> 3 )  then 3 else 2 ))
          Then ( you need to invert/mixed to match the pattern
..........
Deals with going block by block. Passing the correct blocks back....
arnoldCommented:
The unpivot example in https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Addresses your issue directly.

Using cte ...
Do you want to go this route this time?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use this solution so in the future in you'll add more columns you'll only need to add the respective UNION ALL statement.
UPDATE ClubList 
SET MinDate = (SELECT MIN(D.Dates)
		FROM (SELECT EntryDate As Dates
			FROM [ClubList] T1
			WHERE T1.LastName = [ClubList].LastName
			UNION ALL
			SELECT CreationDate
			FROM [ClubList] T1
			WHERE T1.LastName = [ClubList].LastName
			UNION ALL
			SELECT RandDate
			FROM [ClubList] T1
			WHERE T1.LastName = [ClubList].LastName
			UNION ALL
			SELECT LoggingDate
			FROM [ClubList] T1
			WHERE T1.LastName = [ClubList].LastName
			UNION ALL
			SELECT ExpireDate
			FROM [ClubList] T1
			WHERE T1.LastName = [ClubList].LastName) D)

Open in new window

NOTE: This solution works on the assumption that LastName is the primary key of the table. If is not you'll need to change the WHERE clause to use exactly the primary key column(s).
Scott PletcherSenior DBACommented:
If you're at least at SQL 2005, this is fairly straightforward, and easily extensible, using the VALUES table-constructor clause:

UPDATE cl
SET MinDate = ca1.min_date
FROM ClubList cl
CROSS APPLY (
    SELECT MIN(date) AS min_date
    FROM (
        VALUES(EntryDate),(CreationDate),(RandDate),(LoggingDate),(ExpireDate)
    ) AS dates(date)
) AS ca1
Vitor MontalvãoMSSQL Senior EngineerCommented:
Nice solution Scott.
Scott PletcherSenior DBACommented:
Thank you.

But one CORRECTION:

While CROSS APPLY is available in SQL 2005, the VALUES table-constructor is not available until SQL 2008.  If you are in SQL 2005, let me know, we just have to structure the CROSS APPLY a little differently.
brgdotnetcontractorAuthor Commented:
Thank you for the Brilliant help. You are all so talented.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.