selecting field with earliest date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

selecting field with earliest date

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-05-02 : 14:58:07

This should be simple, but I can't figure it out. I have a table with 5 date fields. I need to determine the earliest date of the 5 fields and return the field name and date value.

I'm only querying one row. Of that row, I need to return the earliest date and the field name.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-02 : 15:36:45

I'd do this in VBScript / ASP, but if it's got to be done in SQL, you could use CASE or IF

SELECT @Col1 = Col1, @Col2 = Col2, @Col3 = Col3, etc...

SET @Mincol = @Col1
IF @Mincol > @Col2
SET @MinCol = @Col2
IF @Mincol > @Col3
SET @MinCol = @Col3
IF @Mincol > @Col4
SET @MinCol = @Col4
etc..

Pretty long - Occam wouldn't approve. Anyone have a shorter solution?

davidliv
Starting Member

45 Posts

Posted - 2004-05-02 : 15:38:06

ooops. I wasn't clear enough. I'm only searching one row. Of that row I need to find the Min Date value and return the date value and the field name.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-02 : 17:19:33

This usually indicates a problem with your design.... please give us your table layout. Generally in SQL, you should not be returning column names -- you should be returning DATA not the names of objects in your DB.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-02 : 19:46:44

quote:
Originally posted by davidliv

ooops. I wasn't clear enough. I'm only searching one row. Of that row I need to find the Min Date value and return the date value and the field name.

The post I made was for a single row. Can you post the SELECT and the names of the columns?

davidliv
Starting Member

45 Posts

Posted - 2004-05-03 : 08:52:01

The table structure looks something like this. I have a report which needs to provide the event name, and the earliest start date of the regions.

eid EventName USStart EUStart SAStart GlobalStart
001 Acme1 4/1/2004 8/1/2004 4/30/2004 1/1/2004

I don't think I have a database structure issue.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-03 : 09:44:28

And the query I posted does not work because ...

davidliv
Starting Member

45 Posts

Posted - 2004-05-03 : 10:39:31

All I'm getting min date, but I don't know which field returned this value.

DECLARE @US datetime
DECLARE @SA datetime
DECLARE @JP datetime
DECLARE @GL datetime
DECLARE @MinCol datetime

SELECT
@EU = tblevent.EUlaunch,
@US = tblevent.USlaunch,
@SA = tblevent.SAlaunch,
@JP = tblevent.JPlaunch,
@GL = tblevent.GLlaunch,

FROM tblevent

WHERE tblevent.event_id = 139

SET @Mincol = @EU
IF @MinCol > @US
SET @Mincol = @US
IF @MinCol > @SA
SET @Mincol = @SA
IF @MinCol > @JP
SET @Mincol = @JP
IF @MinCol > @GL
SET @Mincol = @GL

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-03 : 12:39:48

It's not pretty, but how about this:

Select MAX(x.sdate) source_date,
Case Right(MAX(x.sdate),1)
WHEN '1' THEN 'USStart'
WHEN '2' THEN 'EUStart'
WHEN '3' THEN 'SAStart'
WHEN '4' THEN 'GlobalStart'
END source_col FROM
(
SELECT CONVERT(VARCHAR,USStart,126) + '1' r FROM tblevent WHERE eid = 139
UNION ALL
SELECT CONVERT(VARCHAR,EUStart,126) + '2' r FROM tblevent WHERE eid = 139
UNION ALL
SELECT CONVERT(VARCHAR,SAStart,126) + '3' r FROM tblevent WHERE eid = 139
UNION ALL
SELECT CONVERT(VARCHAR,GlobalStart,126) + '4' r FROM tblevent WHERE eid = 139
) x,
tblevent WHERE eid = 139

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-03 : 13:17:59

I am sooooo not in love with this...

Gotta be a way to do 1 access to the table...



USE Northwind
GO

DECLARE @x int
SELECT @x = 10248

SELECT TOP 1 Col1, MIN_Date 
      FROM ( 
	   SELECT 'OrderDate' AS Col1, OrderDate AS MIN_Date
	     FROM Orders
	    WHERE OrderId = 10248
	UNION ALL
	   SELECT 'RequiredDate' AS Col1, RequiredDate AS MIN_Date
	     FROM Orders
	    WHERE OrderId = 10248
	UNION ALL
	   SELECT 'ShippedDate' AS Col1, ShippedDate AS MIN_Date  
	     FROM Orders
	    WHERE OrderId = 10248
) AS XXX
ORDER BY MIN_Date
GO

Brett

8-)

davidliv
Starting Member

45 Posts

Posted - 2004-05-03 : 13:51:20

Here is what I've ended up with. Any other ideas or opinions on this approach?

DECLARE @EU datetime
DECLARE @US datetime
DECLARE @SA datetime
DECLARE @JP datetime
DECLARE @GL datetime
DECLARE @MinCol datetime
DECLARE @MinFld varchar(2000)

DECLARE @US datetime
DECLARE @SA datetime
DECLARE @JP datetime
DECLARE @GL datetime
DECLARE @MinCol datetime

SELECT
@EU = tblevent.EUlaunch,
@US = tblevent.USlaunch,
@SA = tblevent.SAlaunch,
@JP = tblevent.JPlaunch,
@GL = tblevent.GLlaunch,

FROM tblevent

WHERE tblevent.event_id = 139

SET @MinCol = '12/1/2200'
IF @EU IS NOT NULL
Begin
SET @Mincol = @EU
SET @MinFld = 'EU'
End
IF @MinCol > @US AND @US IS NOT NULL
Begin
SET @MinFld = 'US'
SET @Mincol = @US
End
IF @MinCol > @SA AND @SA IS NOT NULL
Begin
SET @MinFld = 'SA'
SET @Mincol = @SA
End
IF @MinCol > @JP AND @JP IS NOT NULL
Begin
SET @MinFld = 'JP'
SET @Mincol = @JP
End
IF @MinCol > @GL AND @GL IS NOT NULL
Begin
SET @MinFld = 'GL'
SET @Mincol = @GL
End

Print @MinFld
Print @MinCol

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-03 : 14:08:56

Well, I don't like mine, but, you sure that works?

Brett

8-)

davidliv
Starting Member

45 Posts

Posted - 2004-05-03 : 15:32:29

Seems to work fine. I tested with a combination of fields values and even null values.

I'm using it as a User Defined Function and calling that function from another stored proc. Don't seem to have any performance problems so far.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-03 : 17:12:37

quote:
Originally posted by davidliv

The table structure looks something like this. I have a report which needs to provide the event name, and the earliest start date of the regions.

eid EventName USStart EUStart SAStart GlobalStart
001 Acme1 4/1/2004 8/1/2004 4/30/2004 1/1/2004

I don't think I have a database structure issue.

here's what I would have:


Events:

eid EventName  ... etc...
--- ---------  ----------
001 Acme       ...

Regions:

Region   RegionDescription   .. etc ...
-------  -----------------   ----------
US       United States       ...
EU       Europe              ...
SA       ????                ...
Global   Global              ...

EventRegions:

eid Region StartDate  .. etc ...
--- ----   ---------  ----------
001 US     4/1/2004   ...
001 EU     8/1/2004   ...
001 SA     4/30/2004  ...
001 Global 1/1/2004   ...

Now, you can have unlimited Regions and handle it easily if they change. And you can store more information about each of those Event/Region combinations without having repeating columns in your table, such as estimated dates vs actual vs personell vs anything else you might need to track. Finally, you can store all of the Regions in a master table for information such as a full name for that region, base currency, home office, contacts, etc ...

It's all part of a normalized database design.

And the best part?

To return the earliest start date and all of the information for that date (such as the type), all you do is:


Select top 1 * from EventStartDates order by StartDate ASC

and you can join to Region or Event for any other information you'd like to return in this query.

- Jeff

davidliv
Starting Member

45 Posts

Posted - 2004-05-04 : 13:35:49

I agree with your ideas of data structure, but for this application there is no need to store any additional information per event nor will there be any more than the 5 regions listed. This single data structure allows me to update all event information with one hit to the database.

Changing the data structure is not a solution in this case.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-04 : 13:40:53

quote:
Originally posted by davidliv

Changing the data structure is not a solution in this case.

There's always that damn deNormalization thing getting in the way again....

It's not an option because it's so entreanched in the code, or because it's a third party vendor?

I vote 3rd party vendor...

Let's take a poll

(I don't think there is a third choice...anyone?)

Brett

8-)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-04 : 14:18:44

quote:
Originally posted by davidliv

...for this application there is no need to store any additional information per event nor will there be any more than the 5 regions listed.

Famous last words.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-05-05 : 14:23:09

I posted this in response to another thread. But it should work just the same.

In his thread he wanted the results to be returned in this format

dates.cola, dates.colb, The Max Date

So for yours I'll just replace the Max with Min!

SELECT dates.cola,
dates.colb,

(SELECT Min(theDates.SomeDate)
FROM (SELECT dates.date1 as SomeDate
UNION
SELECT dates.date2 as SomeDate
UNION
SELECT dates.date3 as SomeDate
UNION
SELECT dates.date4 as SomeDate
UNION
SELECT dates.date5 as SomeDate
UNION
SELECT dates.date6 as SomeDate) theDates) AS MaxDate
FROM dates

Dustin Michaels

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-05 : 19:51:24

I'm not sure what Dustin's query will return (cola, colb, and the min date aliased as MaxDate it appears) but Davidliv wants to know which column contained the max date and what that date was.

My earlier answer will work but you would have to parse result string to find out what the date and source column were, and it does query the table multiple times.

If you can create a function in the db, you can also try this:

Create function f_latest(@d1 datetime, @d2 datetime) returns datetime
as
begin
return case when @d1 > @d2 then @d1 else @d2 end
end

SELECT
CASE dbo.f_latest( dbo.f_latest( dbo.f_latest(USStart, EUStart), SAStart ) , GlobalStart )
WHEN USStart THEN 'USStart'
WHEN EUStart THEN 'EUStart'
WHEN SAStart THEN 'SAStart'
WHEN GlobalStart THEN 'GlobalStart'
END Source,
dbo.f_latest( dbo.f_latest( dbo.f_latest(USStart, EUStart ), SAStart ) , GlobalStart ) TargetDate
FROM tblevent WHERE eid = 139

That will return the column name containing the greatest date, and the date in that column.

If you can't create a function you can rewrite the above replacing function references with case statements but it will be very ugly.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-05-05 : 20:25:30

Heres the modified code that will return the field name too.

SELECT (SELECT Min(theDates.SomeDate)
FROM (SELECT dates.date1 as SomeDate
UNION
SELECT dates.date2 as SomeDate
UNION
SELECT dates.date3 as SomeDate
UNION
SELECT dates.date4 as SomeDate
UNION
SELECT dates.date5 as SomeDate
UNION
SELECT dates.date6 as SomeDate) theDates) AS MinDate

--This here returns the name of column
CASE WHEN MinDate= dates.date1 THEN "Date1"
WHEN MinDate= dates.date2 THEN "Date2"
WHEN MinDate= dates.date3 THEN "Date3"
WHEN MinDate= dates.date4 THEN "Date4"
WHEN MinDate= dates.date5 THEN "Date5"
END AS TheMinDateField

FROM dates

******EDIT*******

I'm not 100% sure if you can compare the derived column MinDatewith date1 date2, etc.

If that dosnt work try this instead.

SELECT (SELECT Min(theDates.SomeDate)
FROM (SELECT dates.date1 as SomeDate
UNION
SELECT dates.date2 as SomeDate
UNION
SELECT dates.date3 as SomeDate
UNION
SELECT dates.date4 as SomeDate
UNION
SELECT dates.date5 as SomeDate
UNION
SELECT dates.date6 as SomeDate) theDates) AS MinDate

--This here returns the name of column
CASE WHEN dates.date1 IN(MinDate) THEN "Date1"
WHEN dates.date2 IN(MinDate) THEN "Date2"
WHEN dates.date3 IN(MinDate) THEN "Date3"
WHEN dates.date4 IN(MinDate) THEN "Date4"
WHEN dates.date5 IN(MinDate) THEN "Date5"
END AS TheMinDateField

FROM dates

Dustin Michaels

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources