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

jkofte
jkofte used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

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

Open in new window

Top Expert 2011
Commented:
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

Author

Commented:
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