[RESOLVED] Get last date from two tables

[PENNYSTOCK]

I think this should be easy, but I just can figure it out. I want to make a SQL View that has a [Part No] and the last activity date. The activity date should come from either the order table or the shipping table. I want the SQL to be able to return my whole part master table with the last ACTIVITY date.

Table 1 PartMaster
Fields: [Part No]

Table 2 Orders
Fields: [PART NO], [ORD DATE]

Table 3 Shipping
Fields [PART NO], [SHIP DATE]

Now I want to end up with a view that has

[PART NO], [LAST ACTIVITY DATE]

[amrita]

Try this !

Select top 1 Orders.*
from Orders inner join PartMaster
On Orders.[Part No] = PartMaster.[Part No]
Order By [ORD DATE] desc

[PENNYSTOCK]

OK, I have a query that returns three columns

Part NO   MaxInvDate                       MaxOrdDate
88-829	1998-08-20 00:00:00.000	1998-08-31 00:00:00.000
88-832	1998-07-22 00:00:00.000	1998-08-03 00:00:00.000
88-843	1998-10-16 00:00:00.000	1999-01-13 00:00:00.000
88-844	1999-02-05 00:00:00.000	1999-02-12 00:00:00.000

I want to get a fourth column that has the maximum of the second and third column.

The resultls should look like this.

Part NO   MaxInvDate                       MaxOrdDate                    Max Date  
88-829	1998-08-20 00:00:00.000	1998-08-31 00:00:00.000  1998-08-31 00:00:00.000
88-832	1998-07-22 00:00:00.000	1998-08-03 00:00:00.000  1998-08-03 00:00:00.000
88-843	1999-10-16 00:00:00.000	1999-01-13 00:00:00.000  1999-10-16 00:00:00.000
88-844	1999-02-05 00:00:00.000	1999-02-12 00:00:00.000  1999-02-12 00:00:00.000

I tried this, but does not work

select [Part No], MaxInvDate, MaxOrdDate, iif(MaxInvDate > MaxOrdDate, MaxInvDate, MaxOrdDate) as myMaxDate
from myTable

Please help!

Dave

[amrita]

write this!

select [Part No], MaxInvDate, MaxOrdDate, 
CASE
  WHEN MaxInvDate > MaxOrdDate THEN MaxInvDate
  WHEN MaxInvDate < MaxOrdDate THEN MaxOrdDate
END AS myMaxDate
from myTable