Min date from 3 date columns 3

[end922]

Greetings, I have a table that has a Suspened Date, Approved Date and a Denied date. Some records can have a date in all 3 fields, some records can have a date in 1 and not the other, some won't have a date in any field and so on...
I am trying to get to the minimium date of all 3 fields and if the date fields are null then show a null. I have tried multiple iff statments but it's not working so well.
I am doing this in an access query. The resluts will be exported to excel then distributed.

Any suggestions would greatly be appreciated.

loan_num SS DD AA
1 02/08/2008
2 02/08/2008
3 01/16/2008
4 01/07/2008 01/14/2008
5
6 01/28/2008 02/05/2008
7 01/08/2008 01/10/2008
8 01/09/2008 01/30/2008
9 01/08/2008
10
11 02/05/2008 04/30/2008 01/11/2008




Thanks
E

 

[PWise]

select loadnumber ,min(dat) as dd(
select loadnumber ss as dat
union
select loadnumber dd as dat
union
select loadnumber aa as dat)a
group by loadnumber

 

[end922]

Where would the table name appear? Table name is MIN

 

[PWise]

Sorry left out from syntex

select loadnumber ,min(dat) as dd from (
select loadnumber ss as dat from tablename
union 
select loadnumber dd as dat from tablename
union 
select loadnumber aa as dat from tablename
)a
group by loadnumber

 

[lespaul]

it would be in each SELECT statement:

select a.loadnumber , DateType, min(a.theDate) as "TheDate"
FROM
(select loadnumber, ss as "theDate", "ss" as "DateType" FROM MIN
union
select loadnumber, dd, "dd" FROM MIN 
union
select loadnumber, aa, "aa" FROM MIN) a
group by loadnumber, DateType

however, this won't show you any nulls, it will show you the MIN date for each loadnumber.
[tt]
LoadNumber DateType TheDate
1 SS 02/08/2008
2 SS 02/08/2008
3 SS 01/16/2008
4 SS 01/07/2008
6 SS 01/28/2008
7 SS 01/08/2008
8 SS 01/09/2008
9 SS 01/08/2008
11 AA 01/11/2008[/tt]

notice 5 and 10 are missing...
Leslie

PS. The UNION query normalizes your data

 

[end922]

Thank You BOTH. Pretty slick I tell ya.

Eric

 

[PWise]

lespaul:

You are right that it wiilnot show null
Have A Star

end922

to Show null create a left join

Select loadnumber 
from min
left join(select loadnumber ,min(dat) as dd from (
select loadnumber ss as dat from tablename
union 
select loadnumber dd as dat from tablename
union 
select loadnumber aa as dat from tablename
)a
group by loadnumber)b
on b.loadnumber=min loadnumber

 

[end922]

pwise your suggestion does show a null record. in this example the loan was cancelled before it ever was approved, suspended or denied therefore it has none of the 3 dates. Your query returned a null record.

loan_num SS DD AA
40916###

loan_num dd
40916###

Thanks again both of you.

 

[PWise]

end99:
if my first example works for you it is fine with me I dont have time today to test my tip today but i know that with aggeate function that null dont show

 

[end922]

I tested it for you. I compaired the results from below to my "mins" table. All the records that did not have a date in either of the columns a null value was returned.




SELECT a.loan_num, Min(a.dat) AS dd INTO InitUpdate
FROM [select loan_num, ss as dat from mins
union
select loan_num, dd as dat from mins
union
select loan_num, aa as dat from mins
]. AS a
GROUP BY a.loan_num;