Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oldest date of 4 assigned to variables

Posted on 2014-01-20
6
Medium Priority
?
261 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 100 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 800 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

719 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