Earliest date...

[Scorez2000]

Think this is a tricky one.

Imagine I have 4 date fields.

date1
date2
date3
date4

I'm running a query to pull out date1. But then the user decides that they don't necessarily want date1. They want the lowest/earliest of the 4 dates.

Anyone any idea how to do this? It needs to be done in various places, one of which has 12 different date fields, and they want the earliest date.

All help would be much appreciated.

Wayne.

 

[r937]

try this:

select iif(date1 >= date2
       and date1 >= date3
       and date1 >= date4, date1,
       iif(date2 >= date1
       and date2 >= date3
       and date2 >= date4, date2,
       iif(date3 >= date1
       and date3 >= date2
       and date3 >= date4, date3, date4))) 
           as lowestdate
  from yourtable

clumsy? you bet

any chance you can change the table design?

a normalized database wouldn't give you these problems



rudy

http://r937.com/

SQL Consulting

 

[Scorez2000]

That's going to get even clumsyer when we attempt the 12 dates.

The database is normalised.

It's a piece of software tracking the interior decoration of apartment bathrooms. They need some reports to show how well they are doing.

The activities are split into sections. So one section may have 4 activities, and seeing as the activities are not always done in the same order, we need to take the earliest date from the 4 activities.

I can't see how changing the architecture of the database would make this any easier.

 

[r937]

i beg to disagree

12 date fields on the same row is not normalized

a normalized relationship would make the query easier because you could say

select foo
     , bar
     , ( select min(thedate)
           from activitytable 
          where fk = x.pk )  as lowestdate
  from yourtable x

no matter if there were 12 activities or a hundred


rudy

http://r937.com/

SQL Consulting

 

[PHV]

For your 12 date fields issue, I suggest you to create a normalization query, i.e. an union query of 12 select instructions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Scorez2000]

r937,

That implies it's one activity.

It's not. It's 4 activities each with a date each, and we want the earliest of these dates.

Anyway, I basically told them it was a load of hassle and we can't do it.

 

[PHV]

Have you tried the UNION query to get a normalized dataset ?
Any chance you could be more specific on your database schema to help us to help you.
I basically told them ... we can't do it
Are you sure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

excuse, please, but what implies what is one activity?

you can't just drag up an old thread like that and expect to continue on with the converstaion as if it were yesterday

you say "4 activities each with a date each"

would you please show your table layout so we can determine what type of sql syntax will help you?



rudy

http://r937.com/

SQL Consulting