Solved

Oldest date of 4 assigned to variables

Posted on 2014-01-20
6
248 Views
Last Modified: 2014-01-20
Happy Monday!  I am new to SQL coding and I want to look for a condition where the oldest date value is determined and then assigned to a new variable.  Example:

Which of these four is the oldest (by date) and return it as a new variable @Oldest.  These four variables will never have the same value.

@var1 = 01-20-14 12:51:00.000
@var2 = 01-20-14 12:52:00.000
@var3 = 01-20-14 12:53:00.000
@var4 = 01-20-14 12:54:00.000

@Oldest = ????

How would I structure this in SQL?
0
Comment
Question by:avatech
6 Comments
 
LVL 4

Author Comment

by:avatech
ID: 39794829
Each of these variables is selected from different tables across 4 databases.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 25 total points
ID: 39794834
I'm not aware of a single function that can pull this off, so the only solution I have is to insert all of the variables into a temp table, and then do a MIN
Declare @var1 datetime = '01-20-14 12:51:00.000'
Declare @var2 datetime = '01-20-14 12:52:00.000'
Declare @var3 datetime = '01-20-14 12:53:00.000'
Declare @var4 datetime = '01-20-14 12:54:00.000'

Declare @oldest datetime 

CREATE TABLE #min (dt datetime) 
INSERT INTO #min (dt) 
VALUES (@var1), (@var2), (@var3), (@var4)

SELECT @oldest = MIN(dt) FROM #min

-- View the result
SELECT @oldest

Open in new window

0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 200 total points
ID: 39794842
The old fashioned way

declare @min DATETIME
if @var1 <= @var2
   SET @min = @var1
else
   SET @min = @var2

if @min <=@var3
   SET @min = @var3

if @min <= @var4
   SET @min = @var4

select @min

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 25 total points
ID: 39794854
>Each of these variables is selected from different tables across 4 databases.

you could do it with a plan MAX() over the UNION of the 4 tables...
select max( dt )
  from  ( select date_field dt from table1 ...
         union all  select date_field from table2 ...
         union all  select date_field from table3 ...
         union all  select date_field from table4 ...
  ) sq 

Open in new window

the ... stands for WHERE ( and eventually GROUP BY ) stuff you might need ... just showing the syntax

you can of course do that with variables also:

select max( dt )
  from  ( select @var1 dt  
         union all  select @var2
         union all  select @var3
         union all  select @var4
  ) sq 

Open in new window

0
 
LVL 4

Author Comment

by:avatech
ID: 39794872
Thanks for the quick responses!  I need to keep it as simple as possible.  I am setting up a condition for NAGIOS to show me the oldest of the four dates.  So far the Old Fashioned seems most appropriate but I do like the first.
0
 
LVL 4

Author Closing Comment

by:avatech
ID: 39795067
I liked all of the options and appreciate the rapid response.  The best fit for me that I was able to test was the "Old Fashioned" although I liked the temp table using MIN.  The third option seemed very efficient as well -- thank you all for contributing!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question