SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get the nearest date from 3 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/11/2012 :  03:38:58  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/11/2012 :  03:46:55  Show Profile  Reply with Quote

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


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

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/11/2012 :  04:09:02  Show Profile  Reply with Quote
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..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/11/2012 :  04:13:24  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/11/2012 :  04:14:24  Show Profile  Reply with Quote
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/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/11/2012 :  04:24:13  Show Profile  Reply with Quote
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.

Edited by - Villanuev on 01/11/2012 04:24:59
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/11/2012 :  04:53:50  Show Profile  Reply with Quote
Done...

I place another close parenthesis after the e

ORDER BY Shipdate)e)

Thank you very much...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/11/2012 :  05:51:36  Show Profile  Reply with Quote
wc

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

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/12/2012 :  00:29:56  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/12/2012 :  02:08:22  Show Profile  Reply with Quote
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/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/12/2012 :  03:34:46  Show Profile  Reply with Quote
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

Edited by - Villanuev on 01/12/2012 03:41:56
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/12/2012 :  03:42:23  Show Profile  Reply with Quote
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/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/12/2012 :  04:35:37  Show Profile  Reply with Quote
Ah okay. but its posible if i will not display the first record?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/13/2012 :  04:32:43  Show Profile  Reply with Quote
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/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/18/2012 :  21:27:35  Show Profile  Reply with Quote
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

Edited by - Villanuev on 01/18/2012 21:37:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000