Get minimum value between multiple columns

Question

I am using SQL Server 2008;

Suppose I have a table "X" with columns "Date1", "Date2", "Dateblah", all of DateTime type.

I want to select the minimum value between three columns, for example (simplified, with date mm / dd / yyyy)

ID Date1 Date2 Dateblah 0 09/29/2011 09/20/2011 09/01/2011 1 01/01/2011 01/05/2011 03/03/2010 ID MinDate 0 09/01/2011 1 03/03/2010 

Is there a bread and butter team for this?

Thanks in advance.

EDIT: I saw this question. What is the best way to select the minimum value from multiple columns? but, unfortunately, it doesn’t suit me, because I am obligated to do it against normalization, because I report on working with tfs, and the “brute force” case will ultimately be painful if I have 6 cu . 7 columns.

Answer 1

There is no built-in function to return min / max from two (or more) columns. You can implement your own scalar function for this.

In SQL Server 2005+, you can use UNPIVOT to put columns in rows, and then use the MIN function:

 CREATE TABLE [X] ( [ID] INT, [Date1] DATETIME, [Date2] DATETIME, [Date3] DATETIME ) INSERT [X] VALUES (0, '09/29/2011', '09/20/2011', '09/01/2011'), (1, '01/01/2011', '01/05/2011', '03/03/2010') SELECT [ID], MIN([Date]) AS [MinDate] FROM [X] UNPIVOT ( [Date] FOR d IN ([Date1] ,[Date2] ,[Date3]) ) unpvt GROUP BY [ID] 

Answer 2

based on scalar function (from Tom Hunter ):

 SELECT ID, (SELECT MIN([date]) FROM (VALUES(Date1),(Date2),(Dateblah)) x([date])) MinDate FROM TableName 

Answer 3

The implementation of the scalar function:

 CREATE FUNCTION [dbo].[MIN](@a SQL_VARIANT, @b SQL_VARIANT) RETURNS SQL_VARIANT AS BEGIN RETURN ( SELECT MIN([x]) FROM (VALUES(@a),(@b)) x([x]) ) END GO DECLARE @a DATETIME = '12 JUL 2011', @b DATETIME = '20 AUG 2011' SELECT [dbo].[MIN](@a, @b) DECLARE @c INT = 12, @d INT = 32 SELECT [dbo].[MIN](@c, @d) 

Answer 4

Just say the table in which your dates are called sells , and it has two date fields Date1 and Date2 , of which you want a minimum.

 SELECT ( SELECT MIN([x]) FROM (VALUES(Date1),(Date2)) x([x]) ) as minimum FROM sells