How to Select/Update the First Date (or last) in an Inner Join

jkofte used Ask the Experts™
Hi there let me describe my problem quickly

I have 3 tables. Lets Say Contact, Order, Transaction and there are relations accordingly
Fields Are:
Contact: Contact Id, FirstDate, LastDate
1-N Relation On ContactId
Order: OrderId, ContactId, OrderTotal
1-1 Relation On OrderId
Transaction: OrderId, DateOfIssue

My question is this:
I want to Update All Contacts' FirstDate and LastDate fields accordingly in one query.
I mean If ContactA has 5 Orders (and 5 Transactions) I want to set the date of first transaction as FirstDate and date of last transaction as LastDate in Contact.

Thanks All.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Something like that
;with CTE(ContactId, FirstDate, LastDate)
	select o.ContactID, MIN(t.DateOfIssue) as FirstDate, MAX(t.DateOfIssue) as LastDate
		Transactions t join [Order] o on
			t.OrderId = o.OrderId
	group by o.ContactId
update Contact
	Contact.FirstDate = CTE.FirstDate
	,Contact.LastDate = CTE.LastDate
	Contact join CTE on
		Contact.ContactId = CTE.ContactId

Open in new window

Top Expert 2011
try this

update contact
  set firstdate=startdate,lastdate=enddate
 from contact as c
inner join (
select contactid,min(t.dateofissue) as startdate,max(t.dateofissue) as enddate
 from transaction as t
 inner join order as o
    on t.orderid=o.orderid
group by contactid
  ) as x
 on c.contactid=x.contactid
 where c.firstdate<>x.startdate
   or c.lastdate<>x.enddate


Thanks guys for he quick responses. all I needed was to use Min Max Functions for dates :) It is midnight here and my brain is about to turn into meat :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial