Join 3 tables and get the data base on recent date

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

Join 3 tables and get the data base on recent date

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-10 : 02:56:09

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-24 |-----------
268435459707076660|2011-08-14|-----------|2011-08-25

[Code]
[\Code]

Thank you in advance..
JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-10 : 03:36:12

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 Trandate 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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-10 : 04:58:06

I am not able to understand HOW and WHY you want that result - sorry.
I guess you have things in mind that we don't know hence we can't follow you...

No, you're never too old to Yak'n'Roll if you're too young to die.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-15 : 02:32:01

Hi Webfred. sorry the requirements was change. please do not reply on my previous topic. thanks.

BTW, I have another Query..There is a little bit difference..
my rquirement are to pull out the lastest returndate. but when i run my script i have a duplicate records
which have the same esn, supplierid and audit_date but have different failcode and returndate date.
kindly please help me guys to optimize my query


HERE is the SAMPLE TABLE
Create table #Table1(ESN nvarchar(35),Supplierid int,Audit_date datetime)
Insert into #Table1 (esn,Supplierid,Audit_date) values ('268435457406246671',45,'2011-11-13')
Insert into #Table1 (esn,Supplierid,Audit_date) values ('268435456204301204',45,'2011-12-06')


Create table #Table2(ESN nvarchar(35),supplierid int, failcode int,reasoncode nvarchar(50), returndate datetime)
Insert into #Table2(esn,Supplierid,failcode,reasoncode,returndate) values ('268435456204301204',45,'750','UNKNOWN','2011-10-14')
Insert into #Table2 (esn,Supplierid,failcode,reasoncode,returndate) values ('268435456204301204',45,'750','UNKNOWN','2011-07-19')
Insert into #Table2 (esn,Supplierid,failcode,reasoncode,returndate) values ('268435456204301204',45,'741','UNKNOWN','2011-11-19')
Insert into #Table2 (esn,Supplierid,failcode,reasoncode,returndate) values ('268435457406246671',45,'742','UNKNOWN','2011-10-08')
Insert into #Table2 (esn,Supplierid,failcode,reasoncode,returndate) values ('268435457406246671',45,'730','UNKNOWN','2011-11-27')


Drop table #Table1
Drop table #Table2
 [/cpde]

[code]
SCRIPT: 
 Select
	t1.ESN,
	t1.Supplierid,
	t1.Audit_date,
	t2.failcode,
	t2.reasoncode,
	t2.returndate
 From #Table1 t1
 Left Outer Join #Table2 t2
 On t1.esn = t2.esn
and t1.audit_date > t2.returndate

SAMPLE RESULT:
ESN----------------Supplier---AuditDate-falcode--reasonCode--Returndate
----------------------------------------------------------------------
268435457406246671--45--2011-11-13--742--UNKNOWN--2011-10-08
268435456204301204--45--2011-12-06--741--UNKNOWN--2011-11-19

thank you in advance..
JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-15 : 03:42:56

I have to revised my Script. id used the Cross apply and the given result is the same with my requirements.
My Problem when i test the script. it takes time finish the process for 4083 records. almost 15 minutes..Is there something wrong with my script?


Select
	t1.ESN,
	t1.Supplierid,
	t1.Audit_date,
	t2.failcode,
	t2.reasoncode,
	t2.returndate
 From #Table1 t1
 Cross Apply (Select top 1 ESN, Failcode, ReasonCode, Returndate from #Table2 
 Where ESN = t1.ESN and returndate < t1.audit_date order by returndate desc) t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources