Joining 3 tables and obtain the latest transaction data

[jovillanuev]

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

[jovillanuev]

Guys, kindly please take a look of my query..
if you have any idea to optimize this... thanks.

SELECT p.ESN,
p.ReturnDate,
s.Receiptdate AS ESNSalvDate,
e.trandate as AsiaReceiptdate
FROM #USPRODUCT 
OUTER APPLY(SELECT TOP 1 Receiptdate FROM #TableESN s
WHERE ESN = p.ESN AND Receiptdate > p.Returndate ORDER BY Receiptdate )s
OUTER APPLY(SELECT TOP 1 Receiptdate FROM #TableRMA s 
WHERE ESN = p.ESN AND Receiptdate > p.Returndate ORDER BY ReceiptDate)e 
WHERE p.ESN IN ('268435458805382753','268435459707076660')
Order by p.esn

Here is the result:

ESN---------------Returndate--Trandate-----|Receiptdate
268435458805382753|2011-12-19| NULL-------| NULL
268435458805382753|2011-08-08| NULL-------|2011-08-24 --this should be the correct one.
268435458805382753|2011-04-21| 2011-05-03 |2011-08-24 -----this is my concerned, the receiptdate is duplicate.
268435459707076660|2011-08-14| 2011-08-25 |NULL
268435459707076660|2011-12-15| NULL |NULL

Thanks..