How to obtain nearest date from 3 tables

[jovillanuev]

Guys,

Need your help. i need to obtain the nearest date from 3 tables.
attached my scripst. th problem with this scripts it creates double entry in receipt date with the same transaction specially for this ESN "268435459407885398".

SELECT p.ESN,
	   p.ReturnDate,
	   s.Receiptdate,
	   e.Shipdate
FROM #Product p with (nolock)
CROSS APPLY(SELECT TOP 1 Receiptdate
            FROM  #SalvReceipts s with (nolock)
            WHERE ESN = p.ESN
            AND Receiptdate > p.Returndate
            ORDER BY Receiptdate )s
CROSS APPLY(SELECT TOP 1 Shipdate
            FROM  #ESNShip s with (nolock)
            WHERE ESN = p.ESN
            AND Shipdate > p.Returndate
            ORDER BY Shipdate)e
Where p.ESN in ('268435459407885398','268435459401659246')
Order by p.ESN

Here is the data structure and sample result.

Create Table #SalvReceipts
(
  ESN nvarchar(50)
  ReceiptDate DateTime,
  SupplierID int
)
Insert Into #SalvReceipts (ESN,Receiptdate) Values ('268435459407885398','2011-09-22')
Insert Into #SalvReceipts (ESN,Receiptdate) Values ('268435459401659246','2011-05-16')	
Insert Into #SalvReceipts (ESN,Receiptdate) Values ('268435459401659246','2011-08-05')	


Create Table #ESNShip
(
  ESN nvarchar(50)
  Shipdate DateTime,
  SupplierID int
)
Insert Into #ESNShip(ESN,Shipdate) Values ('268435459407885398','2010-12-17 00:00:00.000')
Insert Into #ESNShip(ESN,Shipdate) Values ('268435459407885398','2011-04-12 00:00:00.000')	
Insert Into #ESNShip(ESN,Shipdate) Values ('268435459407885398','2011-11-10 00:00:00.000')	
Insert Into #ESNShip(ESN,Shipdate) Values ('268435459401659246','2011-05-31 00:00:00.000')	
Insert Into #ESNShip(ESN,Shipdate) Values ('268435459401659246','2011-09-19 00:00:00.000')	

Create Table #Product
(
  ESN nvarchar(50)
  ReturnDate DateTime,
)
Insert Into #Product(ESN,Returndate) Values ('268435459407885398','2011-01-21 00:00:00.000')
Insert Into #Product(ESN,Returndate) Values ('268435459407885398','2011-07-09 00:00:00.000')
Insert Into #Product(ESN,Returndate) Values ('268435459401659246','2011-07-10 00:00:00.000')

Result should like this: it should not be display with ??? receiptdate
ESN-----------------ReturnDate---Receiptdate--Shipdate
--------------------------------------------------------
268435459407885398--2011-01-21--- ??? 2011-09-22---2011-04-12
268435459407885398--2011-07-09--- 2011-09-22---2011-11-10
268435459401659246--2011-07-10--- 2011-08-05---2011-08-19

OR --i will not include with date with values

ESN-----------------ReturnDate---Receiptdate--Shipdate
--------------------------------------------------------
268435459407885398--2011-07-09--- 2011-09-22---2011-11-10
268435459401659246--2011-07-10--- 2011-08-05---2011-08-19

Thank you and regards,

JOV

[BitBlt]

I hope I understood what your desired result set looks like...it's the second group of two-rows? Lines 44 and 45?

Anyway this query will produce that for your limited test data. No promises for the general data population. Maybe it will give you some ideas?

SELECT p.ESN
,max(returndate) as ReturnDate
,(select min(ReceiptDate) from #SalvReceipts where ESN = p.ESN and ReceiptDate > max(returndate)) as ReceiptDate
,(select min(Shipdate) from #ESNShip where ESN = p.ESN and Shipdate > max(returndate)) as Shipdate
FROM #Product p with (nolock)
group by p.ESN
Order by max(returndate), p.ESN

Note that I'm not using CROSS APPLY. That clause is usually used when you want to use the outer table row and apply to table-valued functions or multi-row return sets. Your subqueries return only a single row per outer table row so it is probably not the best choice here.

I tested this query on SQL2008 only, using your temp table defs and sample data. Good luck!

[jovillanuev]

Thank you very much bitbit...
By the way i have another query..

Based on this query How would i check from another table if those ESNs under the WHERE clause is exist or does not exist?
Is this possible if I will will put a case statement if not exist "Non RMA Returns", if exist "RMA Returns" 
Where should this new statement should be inserted? Thanks.
Here is the sample table to check if ESN is exist

TABLE1
ESN---------------------------------------------------
268435458811099855
268435458812604611

Select 
salv.ESN,
us.USReturndate, 
salv.ReceiptDate as AsiaReceiptdate
From ESNSalvReceipts salv with (nolock)
CROSS APPLY(Select MAX(returndate)as USReturndate 
From USProductRecovery
Where salv.ESN = ESN
and salv.ReceiptDate > ReturnDate) as us 
Where ESN in ('268435458811099855',
'268435458104460544',
'268435458812604611',
'268435457405189769',
'268435457409015924',
'268435458806853261',
'268435458402312284')
and salv.ReceiptDate > '2011-01-01' and salv.ReceiptDate <='2011-12-31'
Order by salv.ESN

Thanks,

JOV

[BitBlt]

If your original question is solved, you should mark this thread as solved and open a new thread with your new question.

And to answer, you could either do a left join where the value is not null, or do a "where exists (select 1 from mynewtable where value = mytestvalue)".

[jovillanuev]

Thanks BitBIt..