Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Oldest date of 4 assigned to variables

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
avatech
Asked:
avatech
3 Solutions
 
avatechAuthor Commented:
Each of these variables is selected from different tables across 4 databases.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Surendra NathTechnology LeadCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
avatechAuthor Commented:
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
 
avatechAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now