How to get the nearest date from 3 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the nearest date from 3 tables

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 03:38:58

Guys,

My objective is to get the returndate(#Product) with the nearest date from receiptdate(#SalvReceipts) and shipdate(#ESNShip).

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

Create Table #ESNShip
(
ESN nvarchar(50)
Shipdate DateTime,
SupplierID int
)
Insert Into #ESNShip(ESN,Shipdate, SupplierID) Values ('268435459401659246','2011-05-31 00:00:00.000',2)
Insert Into #ESNShip(ESN,Shipdate, SupplierID) Values ('268435459401659246','2011-08-19 00:00:00.000',2)

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

Result:

ESN-----------------ReturnDate---Receiptdate--Shipdate
--------------------------------------------------------
268435459401659246--2011-07-10---2011-08-05---2011-08-19

Thanks,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 03:46:55

[code]
SELECT p.*,s.Receiptdate,e.Shipdate
FROM #Product p
CROSS APPLY(SELECT TOP 1 Receiptdate
FROM #SalvReceipts
WHERE ESN = p.ESN
AND Receiptdate > p.Returndate
ORDER BY Receiptdate )s
CROSS APPLY(SELECT TOP 1 Shipdate
FROM #ESNShip
WHERE ESN = p.ESN
AND Shipdate > p.Returndate
ORDER BY Shipdate)e
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 04:09:02

Nice..Great..Thank you so much...
How did you do this Visakh? can you give me a litle advice on how to analize..

Btw, what if no records found in #SalvReceipts but need to display the records of #Product and #ESNShip..

Thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 04:13:24

welcome

If you cant guarantee that records will be present in other two tables. use this instead


SELECT p.*,s.Receiptdate,e.Shipdate
FROM #Product p 
OUTER APPLY(SELECT TOP 1 Receiptdate
            FROM  #SalvReceipts
            WHERE ESN = p.ESN
            AND Receiptdate > p.Returndate
            ORDER BY Receiptdate )s
OUTER APPLY(SELECT TOP 1 Shipdate
            FROM  #ESNShip
            WHERE ESN = p.ESN
            AND Shipdate > p.Returndate
            ORDER BY Shipdate)e

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 04:14:24

see this to understand more on apply operator which I've applied here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 04:24:13

Where i should place the where clause? I got an error..

SELECT p.ESN, p.ReturnDate,s.Receiptdate,e.Shipdate
FROM USProductRecovery p with (nolock)
OUTER APPLY((SELECT TOP 1 Receiptdate
FROM ESNSalvReceipts s with (nolock)
WHERE ESN = p.ESN
AND Receiptdate > p.Returndate
ORDER BY Receiptdate )s
OUTER APPLY(SELECT TOP 1 Shipdate
FROM dbo.ESNShip s with (nolock)
WHERE ESN = p.ESN
AND Shipdate > p.Returndate
ORDER BY Shipdate)e
Where p.ESN in ('268435459401659246','268435458814622954','270113179507864837')

Thanks.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 04:53:50

Done...

I place another close parenthesis after the e

ORDER BY Shipdate)e)

Thank you very much...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 05:51:36

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-12 : 00:29:56

Hi Visakh.

just a clarification regarding the scripts.
Is there any effect if i used to much aliasis prefix Like the letter s?
kindly please take a look the script from previous post. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 02:08:22

quote:
Originally posted by Villanuev

Hi Visakh.

just a clarification regarding the scripts.
Is there any effect if i used to much aliasis prefix Like the letter s?
kindly please take a look the script from previous post. Thanks.

No problem if you're using it in different scope

one is inside derived table query and one outside so its ok
but if you're repeating aliases at same level it will throw an error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-12 : 03:34:46

Hi Visakh,

Just for clarification. i notice that it generate multiple records but the receiptdate has the same data
even though it has only one record from #SalvReceipts Tables.
The Receiptdate is greater than from shipdate and returndate
kindly please check the result below like the first records from both sample. thanks.

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')

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

Create Table #ESNShip
(
ESN nvarchar(50)
Shipdate DateTime,
SupplierID int
)
Insert Into #ESNShip(ESN,Shipdate, SupplierID) Values ('268435459407885398','2010-12-17')
Insert Into #ESNShip(ESN,Shipdate, SupplierID) Values ('268435459407885398','2011-04-12')
Insert Into #ESNShip(ESN,Shipdate, SupplierID) Values ('268435459407885398','2011-11-10')

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

Result generated by the script:

ESN-----------------ReturnDate---Receiptdate--Shipdate
--------------------------------------------------------
268435459407885398--2011-01-21--2011-09-22--2011-04-12
268435459407885398--2011-07-09--2011-09-22--2011-11-10

I think the correct Result is like this.
ESN-----------------ReturnDate---Receiptdate--Shipdate
--------------------------------------------------------
268435459407885398--2011-01-21-- NULL --2011-04-12
268435459407885398--2011-07-09--2011-09-22--2011-11-10
RG,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 03:42:23

ok. in that case how do you determine in which row the value 2011-09-22 should come for Receiptdate? as per your logic, both rows satisfy the required criteria

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-12 : 04:35:37

Ah okay. but its posible if i will not display the first record?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 04:32:43

quote:
Originally posted by Villanuev

Ah okay. but its posible if i will not display the first record?

in that case you lose other field values also right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-18 : 21:27:35

Hi Visakh

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

Regards,

JOV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources