January 9, 2015 at 4:40 am
Hi all,
If i have a table with 5 dates in it (amongst other things)....
Is there a way i can get the lowest date of all of them without having to case it all to find the earliest? Just seems like big case statement to me, and i cant think of anything cleaner?
TIA
Dan
January 9, 2015 at 4:50 am
Depending on what you're trying to to select MIN(datecolumn) will do it.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 9, 2015 at 5:05 am
BWFC (1/9/2015)
Depending on what you're trying to toselect MIN(datecolumn)will do it.
Sorry i dont think i have explained myself well.
There are 5 separate columns all with dates in them. I want the minimum of 5 columns.
Min() will just do a (potentially) grouped minimum of one column.
Dan
January 9, 2015 at 5:10 am
danielfountain (1/9/2015)
BWFC (1/9/2015)
Depending on what you're trying to toselect MIN(datecolumn)will do it.Sorry i dont think i have explained myself well.
There are 5 separate columns all with dates in them. I want the minimum of 5 columns.
Min() will just do a (potentially) grouped minimum of one column.
Dan
Is it minimum for each one of 5 columns from the whole dataset or minumum for every row out of the 5 columns. An example of what you have and the output would be helpful.
January 9, 2015 at 5:12 am
I did think your question was over-thinking things with CASE statements in there. You're right, MIN() won't work. Can you post some sample data please? This sounds like an interesting problem.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 9, 2015 at 5:29 am
Sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS [randomDateTime1],
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS [randomDateTime2],
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS [randomDateTime3],
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS [randomDateTime4],
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS [randomDateTime5]
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
Based on the above, how about this: -
SELECT [base].[ID], [base].[randomDateTime1], [base].[randomDateTime2], [base].[randomDateTime3],
[base].[randomDateTime4], [base].[randomDateTime5], ca.[MIN]
FROM [#testEnvironment] base
CROSS APPLY (
SELECT MIN(.[DATE])
FROM (
SELECT [randomDateTime1]
UNION
SELECT [randomDateTime2]
UNION
SELECT [randomDateTime3]
UNION
SELECT [randomDateTime4]
UNION
SELECT [randomDateTime5]
) sub ( [DATE] )
) ca ( [MIN] );
Which results in: -
ID randomDateTime1 randomDateTime2 randomDateTime3 randomDateTime4 randomDateTime5 MIN
----------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
1 2003-07-28 21:37:36.110 2025-04-03 20:36:22.480 2001-09-17 20:54:18.550 1975-10-20 17:21:53.523 1994-12-16 14:26:40.787 1975-10-20 17:21:53.523
2 1985-04-23 01:11:18.627 1965-03-04 04:16:48.743 1999-02-11 00:02:39.633 1982-08-19 15:17:57.407 1968-12-11 01:49:52.203 1965-03-04 04:16:48.743
3 1973-11-20 20:15:07.717 1948-05-26 22:33:31.610 2026-03-27 16:15:33.197 2010-07-12 06:35:28.427 1961-09-24 04:02:17.773 1948-05-26 22:33:31.610
4 2021-07-03 20:05:22.807 1958-07-30 20:56:29.390 2005-02-16 12:18:45.317 1985-05-09 00:58:03.723 1949-07-25 13:36:39.127 1949-07-25 13:36:39.127
5 1950-07-30 12:09:38.957 1978-03-24 16:52:47.397 1972-11-21 13:14:23.700 1957-06-22 20:08:24.533 2005-11-14 02:11:56.230 1950-07-30 12:09:38.957
(5 row(s) affected)
January 9, 2015 at 6:37 am
Cadavre,
Thats the ticket. I must say i have never used cross apply and i might need to do some research. I dont understand how that part works without any FROM.
Excellcent though.... just i need to swat up.
Thanks for all your help everyone.
Dan
January 9, 2015 at 3:39 pm
The VALUES clause simplifies it considerately, which can be great for longer lists:
CROSS APPLY (
SELECT MIN(date)
FROM (
VALUES([randomDateTime1]), ([randomDateTime2]), ([randomDateTime3]),
([randomDateTime4]), ([randomDateTime5])
) dates ( [DATE] )
) ca ( [MIN] );
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply