March 11, 2010 at 5:56 am
Hi All,
Racking my brain trying to find a way to return the earliest date from 3 different date fields within the same row?
So If I have Account=1, Date = 01/03/2010, Date2 = 02/03/2010 and Date3 = 03/03/2010 what would be the most elegant way to return 01/03/2010 ?
Many thanks,
Wardy.
March 11, 2010 at 6:17 am
WardyWonderland (3/11/2010)
So If I have Account=1, Date = 01/03/2010, Date2 = 02/03/2010 and Date3 = 03/03/2010 what would be the most elegant way to return 01/03/2010?
Maybe:
DECLARE @Sample
TABLE (
account INTEGER NOT NULL,
date1 DATE NOT NULL,
date2 DATE NOT NULL,
date3 DATE NOT NULL
);
INSERT @Sample
(account, date1, date2, date3)
VALUES (1, '20100301', '20100302', '20100303');
SELECT account, Least.date
FROM @Sample S
CROSS
APPLY (
SELECT MIN(Dates.each_date)
FROM (
VALUES
(S.date1),
(S.date2),
(S.date3)
) Dates (each_date)
) Least (date);
Though if you need to do it a lot, I'd write a CLR UDA to simulate the GREATEST and LEAST aggregate functions.
Paul
edit: You wanted the minimum, not maximum!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 6:33 am
Give it a try :
DECLARE @Sample
TABLE (
account INTEGER NOT NULL,
date1 DATETIME NOT NULL,
date2 DATETIME NOT NULL,
date3 DATETIME NOT NULL
);
INSERT @Sample
(account, date1, date2, date3)
VALUES (1, '20100301', '20100302', '20100303');
Select MIN(Date) as Earliest_Date from
(
Select date1 as Date from @Sample where account=1
Union
Select date2 from @Sample where account=1
Union
Select date3 from @Sample where account=1
) TBL
March 11, 2010 at 6:48 am
Just noticed this is the 2005 forum. So, here's a 2005-compatible version of my code:
DECLARE @Sample
TABLE (
account INTEGER NOT NULL,
date1 DATETIME NOT NULL,
date2 DATETIME NOT NULL,
date3 DATETIME NOT NULL
);
INSERT @Sample
(account, date1, date2, date3)
VALUES (1, '20100301', '20100302', '20100303');
SELECT account,
least =
( SELECT TOP (1)
Dates.date1
FROM (
SELECT S.date1 UNION ALL
SELECT S.date2 UNION ALL
SELECT S.date3
) Dates
ORDER BY
Dates.date1
)
FROM @Sample S;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 7:19 am
Many thanks for your replies!
March 11, 2010 at 7:35 am
The scripts on the link below demos two methods for doing this.
MIN/MAX Across Multiple Columns
March 11, 2010 at 8:37 am
Another alternative:
DECLARE @Sample
TABLE (
account INTEGER NOT NULL,
date1 DATETIME NOT NULL,
date2 DATETIME NOT NULL,
date3 DATETIME NOT NULL
);
INSERT INTO @Sample
(account, date1, date2, date3)
SELECT 1, '20100301', '20100302', '20100303' union all
SELECT 1, '20100204', '20100202', '20100201' union all
SELECT 2, '20100402', '20100401', '20100403'
SELECT account, CASE WHEN date1 <= date2 and date1 <= date3 THEN date1
WHEN date2 < date3 THEN date2
ELSE date3 END as Least
FROM @Sample
/Markus
March 11, 2010 at 8:47 am
Hunterwood (3/11/2010)
Another alternative:
DECLARE @SampleTABLE (
account INTEGER NOT NULL,
date1 DATETIME NOT NULL,
date2 DATETIME NOT NULL,
date3 DATETIME NOT NULL
);
INSERT INTO @Sample
(account, date1, date2, date3)
SELECT 1, '20100301', '20100302', '20100303' union all
SELECT 1, '20100204', '20100202', '20100201' union all
SELECT 2, '20100402', '20100401', '20100403'
SELECT account, CASE WHEN date1 <= date2 and date1 <= date3 THEN date1
WHEN date2 < date3 THEN date2
ELSE date3 END as Least
FROM @Sample
/Markus
If any of the date columns are nullable, the case statement is more complicated.
For an example of how to code it, see the example in the link that I posted before.
March 11, 2010 at 8:50 am
CASE seems like a lot more work...? 😎
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 10:48 am
Paul White (3/11/2010)
CASE seems like a lot more work...? 😎
CASE certainly is more work, especially when you are looking at more than 4 columns.
However, it may be faster if performance is very important.
March 12, 2010 at 12:04 am
This is actually slightly more efficient that the TOP/MIN methods:
SELECT U.account,
least = MIN(U.value)
FROM #Sample
UNPIVOT (
value
FOR name IN
(
date01,
date02,
date03
)
) U
GROUP BY
U.account;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 16, 2010 at 6:09 am
DECLARE @Sample
TABLE (
account INTEGER NOT NULL,
date1 DATE NOT NULL,
date2 DATE NOT NULL,
date3 DATE NOT NULL
);
INSERT @Sample
(account, date1, date2, date3)
VALUES (1, '20100301', '20100302', '20100303');
SELECT account , MAX(date) MAX_DATE , MIN(date) MIN_DATE
FROM ( SELECT a.account ,
(case b.number
when 0 then date1
when 1 then date2
when 2 then date3
end) date
FROM @Sample a CROSS JOIN ( SELECT number FROM master.dbo.spt_values WHERE type = 'P' AND number < 3 ) b ) R
GROUP BY account
March 16, 2010 at 6:48 am
vax750,
If you are going to use CASE, Michael's solution is more efficient:
DECLARE @Sample
TABLE (
account INTEGER NOT NULL,
date1 DATE NOT NULL,
date2 DATE NOT NULL,
date3 DATE NOT NULL
);
INSERT @Sample
(account, date1, date2, date3)
VALUES (1, '20100301', '20100302', '20100303');
SELECT account,
least =
CASE
WHEN date1 <= date2 AND date1 <= date3 THEN date1
WHEN date2 < date3 THEN date2
ELSE date3
END,
greatest =
CASE
WHEN date1 > date2 AND date1 > date3 THEN date1
WHEN date2 > date3 THEN date2
ELSE date3
END
FROM @Sample;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 11:16 pm
Poal White
In your advice appears to becoming the many help.
English expresses an intention clumsy, is difficult but
The variable one time when the column is not to the fixation person
Were not [kays] valence 3 of the columns and when being more 3, tried to think.
(While translation site using...)
Many advice wishes. Thanks.
vax750
March 17, 2010 at 11:53 pm
Did you see Michael's link?
Michael Valentine Jones (3/11/2010)
The scripts on the link below demos two methods for doing this.
The examples there show how to extend the method for a greater number of columns.
Translation sites are not perfect yet, are they? 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply