Maximum date in a row (not a column)
Day 2 of a post every day ! I was asked recently how you could get the latest/earliest date field across a row of data, not the most usual request it tends to be the maximum value in a column and then its just a straight forward GROUP BY and MAX in that case. However
given the example above the user wanted for each driver to take the minimum date of birth. the following solution could be used.
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[DriverDetails]') AND type in (N'U'))
DROP TABLE [dbo].[DriverDetails]
GO
CREATE TABLE DriverDetails
( DriverID INT IDENTITY(1,1),
DriverName VARCHAR(20),
Driver1DOB DATETIME,
Driver2DOB DATETIME,
Driver3DOB DATETIME
)
INSERT INTO DriverDetails VALUES
('Bob','01 May 1986','01 June 1986','01 April 1986'),
('Mary','01 May 1976','01 June 1946','01 April 1986'),
('John','01 May 1966','01 June 1956','01 April 1946'),
('Jenny','01 May 1956','01 June 1966','01 April 1986')
SELECT
*
FROM
DriverDetails a
JOIN
(
--Unpivot the data and then do a MAX, MIN ON Driver
SELECT DriverID,MIN(DOB)AS MinDOB,MAX(DOB) AS MaxDOB
FROM
(SELECT DriverID,Driver1DOB,Driver2DOB,Driver3DOB
FROM DriverDetails) p
UNPIVOT
(DOB FOR Employee IN
(Driver1DOB,Driver2DOB,Driver3DOB)
)AS unpvt
GROUP BY DriverID
) b
ON a.DriverID =b.DriverID;