Hi Forumer's,
I have 3 tables, my objective is to compare the returndate from receiptdate and trandate and get the most recent receiptdate or trandate based on returndate.
Create Table #USPRODUCT
(ESN Nvarchar(35), returndate datetime)
Insert Into #USPRODUCT (ESN, returndate) Values ('268435458805382753','2011-04-21')
Insert Into #USPRODUCT (ESN, returndate) Values ('268435458805382753','2011-08-08')
Insert Into #USPRODUCT (ESN, returndate) Values ('268435458805382753','2011-12-19')
Insert Into #USPRODUCT (ESN, returndate) Values ('268435459707076660','2011-08-14')
Insert Into #USPRODUCT(ESN, returndate) Values ('268435459707076660','2011-12-15')
Create Table #TableESN
(ESN Nvarchar(35), receiptdate datetime)
Insert Into #TableESN (ESN,receiptdate) Values (',268435458805382753','2010-12-17')
Insert Into #TableESN (ESN,receiptdate) Values (',268435458805382753','2011-05-03')
Insert Into #TableESN (ESN,receiptdate) Values (',268435459707076660','2011-06-16')
Insert Into #TableESN (ESN,receiptdate) Values (',268435459707076660','2011-08-25')
Create Table #TableRMA
(ESN Nvarchar(35), Trandate datetime)
Insert Into #TableRMA (ESN,Trandate) Values ('268435458805382753','2011-08-24')
--Expected Result:
ESN |ReturnDate|Trandate |Receiptdate
268435458805382753|2011-08-08|2011-08-08 |
268435459707076660|2011-08-14| |2011-08-25
Thank you in advance..
JOV