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