[RESOLVED] Get last date from two tables

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]

Try this !

Code:

---

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

---

OK, I have a query that returns three columns

Code:

---

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.

HTML Code:

---

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

write this!

Code:

---

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

---