Need SQL Query to determine max value from fields in a row

[Bill]

Hello out there;

This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things for
each row.

1) What is the maximun date in any of the date fields in the row.

OR

2) Which field in the row contains the most recent date.

TIA

Bill

[Salad]

Bill wrote:

Hello out there;

This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things for
each row.

1) What is the maximun date in any of the date fields in the row.

OR

2) Which field in the row contains the most recent date.

TIA

Bill

You could write a function. Ex:
Expr1 : GetEarliestDate([Date1],[Date2],[Date3],[Date4])
Expr2 : GetRecentDateDate([Date1],[Date2],[Date3],[Date4])

Private Function GetEarliesDate( d1 As Date....)
If d1 < d2 And D1 < d3 And d1 < d4 then
GetEarliesDate = D1
Else if d2 < d1 And d2 < d3 and d2 < d4 then
...
End Function

Or you could right a hell-atious IIF() statement.

[CDB]

First thought - any time such a problem arises, check the data schema for
improved normalisation.

Second, if that is not appropriate, or possible, create a temporary
normalised table (vertical slices) using a Union subquery:

SELECT A.RowID, Max(A.Date1) AS Latest
FROM [SELECT R.RowID, R.Date1 FROM RowTable AS R
UNION ALL SELECT R.RowID, R.Date2 FROM RowTable AS R
UNION ALL SELECT R.RowID, R.Date3 FROM RowTable AS R
]. AS A
GROUP BY A.RowID;

Clive
"Bill" <va****@rac.ca> wrote in message
news:68**************************@posting.google.c om...

Hello out there;

This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things for
each row.

1) What is the maximun date in any of the date fields in the row.

OR

2) Which field in the row contains the most recent date.

TIA

Bill

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:N4****************@newsread2.news.pas.earthli nk.net:

Bill wrote:

Hello out there;

This may be a challenge but I'm certain it's possible but I can't
seem to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things
for each row.

1) What is the maximun date in any of the date fields in the row.
OR

2) Which field in the row contains the most recent date.

You could write a function. Ex:
Expr1 : GetEarliestDate([Date1],[Date2],[Date3],[Date4])
Expr2 : GetRecentDateDate([Date1],[Date2],[Date3],[Date4])

Private Function GetEarliesDate( d1 As Date....)
If d1 < d2 And D1 < d3 And d1 < d4 then
GetEarliesDate = D1
Else if d2 < d1 And d2 < d3 and d2 < d4 then
...
End Function

Or you could right a hell-atious IIF() statement.

If you really wanted the function to handle many dates, you could
use a parameter array, and then walk the array to check which one
was the max. Check the Help for PARAMETER ARRAY to figure it out.

But it sounds like a probably denormalized structure, though I've
certainly seen situations where you wanted to return the latest of a
handful of independent dates. I've seldom seen more than two or
three, and for those, I've always used my iMax()/iMin() functions.
Here's iMax:

Public Function iMax(X As Long, y As Long)
If X > y Then
iMax = X
Else
iMax = y
End If
End Function

Actually, in looking this up, I see that Trevor Best has already
done the parameter array work for you:

Public Function iMax(ParamArray p()) As Variant
' Idea from Trevor Best in Usenet MessageID
' ri********************************@4ax.com
Dim i As Long
Dim v As Variant

v = p(LBound(p))
For i = LBound(p) + 1 To UBound(p)
If v < p(i) Then
v = p(i)
End If
Next
iMax = v
End Function

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Salad]

David W. Fenton wrote:

Salad <oi*@vinegar.com> wrote in
news:N4****************@newsread2.news.pas.earthli nk.net:

Bill wrote:

Hello out there;

This may be a challenge but I'm certain it's possible but I can't
seem to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things
for each row.

1) What is the maximun date in any of the date fields in the row.
OR

2) Which field in the row contains the most recent date.

You could write a function. Ex:
Expr1 : GetEarliestDate([Date1],[Date2],[Date3],[Date4])
Expr2 : GetRecentDateDate([Date1],[Date2],[Date3],[Date4])

Private Function GetEarliesDate( d1 As Date....)
If d1 < d2 And D1 < d3 And d1 < d4 then
GetEarliesDate = D1
Else if d2 < d1 And d2 < d3 and d2 < d4 then
...
End Function

Or you could right a hell-atious IIF() statement.

If you really wanted the function to handle many dates, you could
use a parameter array, and then walk the array to check which one
was the max. Check the Help for PARAMETER ARRAY to figure it out.

I understood the problem to be there are many roww of data. Each row
has 4 dates. Out of those 4 dates he wanted the min and max dates for
each row.

I'm not sure how a parameter array would assist in this case. When
calling a function in a query, you need to pass the arguments of that
row withing the query or perhaps the key so the function can waht to
process.

I considered a union query but I thought he might want the two results
on the same returned row.

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:RL*****************@newsread2.news.pas.earthl ink.net:

David W. Fenton wrote:

Salad <oi*@vinegar.com> wrote in
news:N4****************@newsread2.news.pas.earthli nk.net:

Bill wrote:

Hello out there;

This may be a challenge but I'm certain it's possible but I
can't seem to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things
for each row.

1) What is the maximun date in any of the date fields in the
row.
OR

2) Which field in the row contains the most recent date.

You could write a function. Ex:
Expr1 : GetEarliestDate([Date1],[Date2],[Date3],[Date4])
Expr2 : GetRecentDateDate([Date1],[Date2],[Date3],[Date4])

Private Function GetEarliesDate( d1 As Date....)
If d1 < d2 And D1 < d3 And d1 < d4 then
GetEarliesDate = D1
Else if d2 < d1 And d2 < d3 and d2 < d4 then
...
End Function

Or you could right a hell-atious IIF() statement.

If you really wanted the function to handle many dates, you could
use a parameter array, and then walk the array to check which one
was the max. Check the Help for PARAMETER ARRAY to figure it out.

I understood the problem to be there are many roww of data. Each
row has 4 dates. Out of those 4 dates he wanted the min and max
dates for each row.

I'm not sure how a parameter array would assist in this case.
When calling a function in a query, you need to pass the arguments
of that row withing the query or perhaps the key so the function
can waht to process.

I considered a union query but I thought he might want the two
results on the same returned row.

Did you try out the function I posted?

What you want is a function that will return the lowest date:

GetLowestDate(Field1, Field2, Field3, Field4, Field5...)

The function I posted will give you that, and it uses a parameter
array so that you can pass it 2 or more arguments and return the
minimum/maximum of all the values you pass it.

Try it -- you'll see that it does exactly what is needed here with
no IIf() and no UNION.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[CDB]

Why use an added-on code manager in a Relational Database when "native" SQL
will do the job?

Clive

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.74...

Salad <oi*@vinegar.com> wrote in
news:RL*****************@newsread2.news.pas.earthl ink.net:

David W. Fenton wrote:

Salad <oi*@vinegar.com> wrote in
news:N4****************@newsread2.news.pas.earthli nk.net:
Bill wrote:

>Hello out there;
>
>This may be a challenge but I'm certain it's possible but I
>can't seem to figure out how.
>
>I have a table that has several date fields, e.g., Date1, Date2,
>Date3, Date4 ... etc. I wish to find out either of two things
>for each row.
>
>1) What is the maximun date in any of the date fields in the
>row.
>
>
>OR
>
>2) Which field in the row contains the most recent date.

You could write a function. Ex:
Expr1 : GetEarliestDate([Date1],[Date2],[Date3],[Date4])
Expr2 : GetRecentDateDate([Date1],[Date2],[Date3],[Date4])

Private Function GetEarliesDate( d1 As Date....)
If d1 < d2 And D1 < d3 And d1 < d4 then
GetEarliesDate = D1
Else if d2 < d1 And d2 < d3 and d2 < d4 then
...
End Function

Or you could right a hell-atious IIF() statement.

If you really wanted the function to handle many dates, you could
use a parameter array, and then walk the array to check which one
was the max. Check the Help for PARAMETER ARRAY to figure it out.

I understood the problem to be there are many roww of data. Each
row has 4 dates. Out of those 4 dates he wanted the min and max
dates for each row.

I'm not sure how a parameter array would assist in this case.
When calling a function in a query, you need to pass the arguments
of that row withing the query or perhaps the key so the function
can waht to process.

I considered a union query but I thought he might want the two
results on the same returned row.

Did you try out the function I posted?

What you want is a function that will return the lowest date:

GetLowestDate(Field1, Field2, Field3, Field4, Field5...)

The function I posted will give you that, and it uses a parameter
array so that you can pass it 2 or more arguments and return the
minimum/maximum of all the values you pass it.

Try it -- you'll see that it does exactly what is needed here with
no IIf() and no UNION.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"CDB" <al***@delete.wave.co.nz> wrote in
news:c7**********@news.wave.co.nz:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.74...

Salad <oi*@vinegar.com> wrote in
news:RL*****************@newsread2.news.pas.earthl ink.net:

> David W. Fenton wrote:
>
>> Salad <oi*@vinegar.com> wrote in
>> news:N4****************@newsread2.news.pas.earthli nk.net:
>>
>>
>>>Bill wrote:
>>>
>>>>Hello out there;
>>>>
>>>>This may be a challenge but I'm certain it's possible but I
>>>>can't seem to figure out how.
>>>>
>>>>I have a table that has several date fields, e.g., Date1,
>>>>Date2, Date3, Date4 ... etc. I wish to find out either of
>>>>two things for each row.
>>>>
>>>>1) What is the maximun date in any of the date fields in the
>>>>row.
>>>>
>>>>
>>>>OR
>>>>
>>>>2) Which field in the row contains the most recent date.
>>>
>>>You could write a function. Ex:
>>> Expr1 : GetEarliestDate([Date1],[Date2],[Date3],[Date4])
>>> Expr2 :
>>> GetRecentDateDate([Date1],[Date2],[Date3],[Date4])
>>>
>>>Private Function GetEarliesDate( d1 As Date....)
>>> If d1 < d2 And D1 < d3 And d1 < d4 then
>>> GetEarliesDate = D1
>>> Else if d2 < d1 And d2 < d3 and d2 < d4 then
>>> ...
>>>End Function
>>>
>>>Or you could right a hell-atious IIF() statement.
>>
>> If you really wanted the function to handle many dates, you
>> could use a parameter array, and then walk the array to check
>> which one was the max. Check the Help for PARAMETER ARRAY to
>> figure it out.
>
> I understood the problem to be there are many roww of data.
> Each row has 4 dates. Out of those 4 dates he wanted the min
> and max dates for each row.
>
> I'm not sure how a parameter array would assist in this case.
> When calling a function in a query, you need to pass the
> arguments of that row withing the query or perhaps the key so
> the function can waht to process.
>
> I considered a union query but I thought he might want the two
> results on the same returned row.

Did you try out the function I posted?

What you want is a function that will return the lowest date:

GetLowestDate(Field1, Field2, Field3, Field4, Field5...)

The function I posted will give you that, and it uses a parameter
array so that you can pass it 2 or more arguments and return the
minimum/maximum of all the values you pass it.

Try it -- you'll see that it does exactly what is needed here
with no IIf() and no UNION.

Why use an added-on code manager in a Relational Database when
"native" SQL will do the job?

Because it's faster?

Now, that might depend on circumstances. If you write your UNION
correctly, it will be a UNION ALL, and those can be very fast, but
they still don't use indexes the same way as non-UNIONs do.

Also, if the result needs to be editable, the UNION won't do the job
(unless you use the UNION as the basis for an elaborate subquery).

Which approach is best depends entirely on what you're trying to
accomplish.

I would definitely say that the use of a UNION suggests to me that
the data is badly denormalized, since the UNIONing your fields into
a single column is a renormalization of multiple columns into a
single one against which you can apply criteria/transformations.

But if you're just wanting the lesser or greater of a handful of
fields for a form display or for a check in a loaded record, a UNION
makes no sense to me.

Let me explain that. I can think of an app of mine where there were
three dates that were independent pieces of information that were
put in a single row (they were truly independent attributes of the
entity being modelled, not three instances of a child entity). When
you were adding child records to the main record, you had to check
that the date of the child record was not greater than the Max() of
the three fields in that record. A UNION on a single record would be
incredible overkill, but the parameter array iMax() function would
make a great deal of sense.

The more records you're processing to get the answer, the more
likely it is that the UNION will be preferrable.

But if you're constantly processing large numbers of records to find
this out, then I'd suggest this vastly increases the likelihood that
your schema is denormalized.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bill]

"CDB" <al***@delete.wave.co.nz> wrote in message news:<c7**********@news.wave.co.nz>...

First thought - any time such a problem arises, check the data schema for
improved normalisation.

Second, if that is not appropriate, or possible, create a temporary
normalised table (vertical slices) using a Union subquery:

SELECT A.RowID, Max(A.Date1) AS Latest
FROM [SELECT R.RowID, R.Date1 FROM RowTable AS R
UNION ALL SELECT R.RowID, R.Date2 FROM RowTable AS R
UNION ALL SELECT R.RowID, R.Date3 FROM RowTable AS R
]. AS A
GROUP BY A.RowID;

Clive
"Bill" <va****@rac.ca> wrote in message
news:68**************************@posting.google.c om...

Hello out there;

This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.

I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ... etc. I wish to find out either of two things for
each row.

1) What is the maximun date in any of the date fields in the row.

OR

2) Which field in the row contains the most recent date.

TIA

Bill

Thanks to those who helped.

I'll take the three approaches to the office tomorrow and see if I can
implement any of the approaches. Just a beginner.

A word of explanation wrt normalization the table deals with a
tracking progress of a document that goes through a number of steps,
not necessarily in order but usually and there is a limited number of
possible events, six in this case. I would have been possible to
track them in a separate table with three columns document, event, and
date. However I chose the easier route with six columns one per date.

There are not a great many rows perhaps several hundred. About the
development environment. The application is a web-based interface for
both input update and review of the records. I'm working in with HTML
and ASP and MS Access database as well as bits of vbscript &
JavaScript. So implementing some of the more advance approaches such
as the parameter array will be pushing my envelope. But after all
that's were the fun is.

Thanks once again for your assistance. I certainly appreciate the
ideas and effort it takes to provide the assistance.

Thank you.

Bill