Solved

Oldest date of 4 assigned to variables

Posted on 2014-01-20
6
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Author Comment

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

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 143

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

617 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