Solved

Oldest date of 4 assigned to variables

Posted on 2014-01-20
6
247 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now