How to update a column value based upon another column

I need to set the MinDate column to the smallest date of other date columns in the same record/row. 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.

Can someone help me a query to do this? I would prefer not to use a cursor if possible. The actual table I am dealing with has about 300 records in it.



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

Here is the table after the min date is entered

LastName  MinDate        EntryDate     CreationDate     RandDate
Smiler         01/22/2010     02/20/2010    01/22/2010       11/20/2010
Greyson     01/20/2007     11/07/2018    03/12/2010       01/20/2007
Wolford     01/07/2010     01/07/2010    01/07/2010       01/20/2015
Lakeson     01/20/2010     04/20/2010    01/20/2010       03/20/1998
Chipper      02/01/1945    02/01/1945    01/20/2010       07/26/2011
SQLQuery2.sql
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
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.

Anoo S PillaiCommented:
This should be simple update, something like

UPDATE <YourTab>
SET MinDate =  << Here put the logic to identify the lowest of the three columns>>

The logic to identify the lowest of the three date can be a nested CASE / equivalent statement like

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
0

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:
Your best bet is to unpivot the table first
Making it
Last name, date type, dates

Using cte you would then update existing table.mindate=min(cte_table.dates)

Let see if I can .....
0
arnoldCommented:
Here is a reference if needed to pivot/unpivot.
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Make sure to use isnull with future date when the value in the table is null.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brgdotnetcontractorAuthor Commented:
I am absolutely stuck on this. I have tried for over an hour for a solution, and can't figure it out. I can't get CTE.

I did try something like this that uses WHERE EXISTS, but I know I am on the wrong path.

DECLARE @entry1 DateTime
DECLARE @create1 DateTime
DECLARE @randDat1 DateTime
DECLARE @miner DateTime
DECLARE @Last varchar(50)


WHERE EXISTS (
UPDATE top(10

select top(1) LastName, @miner= MinDate, @entry1 = EntryDate, @create1 = CreationDate,@randDat1 =RandDate from ClubList where MinDate IS NULL GROUP BY LastName, MinDate,EntryDate,CreationDate,RandDate
PRINT @entry1
PRINT @create1
PRINT @randDat1
PRINT @miner
)
0
brgdotnetcontractorAuthor Commented:
I will work in it some more and post my solution. Thanks.
0
brgdotnetcontractorAuthor Commented:
Not able to figure it out, unless I use a cursor. However the whole reason for posting my question was to avoid using cursors.
0
Anoo S PillaiCommented:
Aren't you using SQL server ?
0
Anoo S PillaiCommented:
I posted a solution earlier, Let me elaborate the concept with a working sample code-


Use TempDB  
go

CREATE TABLE TestTab ( LastName VARCHAR(100) , MinDate DATE , EntryDate DATE , CreationDate DATE, RandDate DATE ) ;

INSERT INTO TestTab ( LastName , MinDate , EntryDate ,CreationDate ,  RandDate )
VALUES  ( 'Smiler', NULL , '02/20/2010', '01/22/2010', '11/20/2010' )

INSERT INTO TestTab ( LastName , MinDate , EntryDate ,CreationDate ,  RandDate )  
VALUES ( 'Greyson' ,    NULL ,     '11/07/2018',    '03/12/2010',        '01/20/2007' )

INSERT INTO TestTab ( LastName , MinDate , EntryDate ,CreationDate ,  RandDate )
VALUES  ( 'Wolford',        NULL   ,   '01/07/2010',    '01/07/2010',       '01/20/2015' )


SELECT * FROM TestTab

UPDATE TestTab
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

SELECT * FROM TestTab

As per my understanding, this what you were looking for.   Could you please check ?
0
brgdotnetcontractorAuthor Commented:
Hi Anoo. Sorry, I thought your solution was pseudocode. I will look it over more thoroughly tonight. This is what I came up with. Probably not the most elegant solution, but it works. I think your solution works, and I will give you most of the points sir.

DECLARE @userData TABLE(
    minimum DateTime
);

DECLARE @entry1 DateTime
DECLARE @create1 DateTime
DECLARE @randDat1 DateTime
DECLARE @miner DateTime
DECLARE @Last varchar(50)

DECLARE @rc INT = 1;
WHILE (@rc > 0 )
BEGIN
            WITH UpdateList AS (
              SELECT TOP 1  * from ClubList
              WHERE MinDate IS NULL
              ORDER BY MinDate DESC
            )

UPDATE UpdateList SET MinDate = '1977-01-22 00:00:00'


SELECT @entry1 = EntryDate,@create1 = CreationDate, @randDat1 = RandDate from ClubList WHERE MinDate = '1977-01-22 00:00:00'
INSERT INTO @userData (minimum) VALUES( @entry1)
INSERT INTO @userData (minimum) VALUES( @create1)
INSERT INTO @userData (minimum) VALUES( @randDat1)
INSERT INTO @userData (minimum) VALUES( @entry1)

SELECT @miner = MIN(minimum) from @userData
 UPDATE      ClubList SET MinDate = @miner WHERE MinDate = '1977-01-22 00:00:00'
 delete @userData
--PRINT @miner

IF NOT EXISTS (SELECT TOP 1  * from ClubList WHERE MinDate IS NULL)
BEGIN
      SET @rc = -1
END
END
0
brgdotnetcontractorAuthor Commented:
Anoo you are brilliant. Thank you.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.