Search Query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Search Query

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-20 : 08:33:38

Hi Forumer's

My requirements:
I need to check if the ESN is exist from table2 and need to get the supplierID. In table1 i have 446,664 records but when i tried to use the left outer join to table2 i get 200 plus excess records from table1. what is the best way get the exact records from table1 to table2.

Here is my script, need your input or kindly please check my script is correct based on my requirements. thanks in advance.

[Code]
My query:
select
us.ESN,
us.ReturnDate,
rma.SupplierID
from Table1 us
Left Outer Join (Select ESN, supplierid from Table2 group by ESN, SupplierID) as rma
On rma.ESN = us.ESN
--and rma.SupplierID=45
Where Returndate >= '01/01/11' and Returndate < '06/01/12'
and rma.SupplierID=45
order by us.ESN

Data:
Declare @table1 TABLE (ESN Nvarchar(35), Returndate datetime)

INSERT @Table1
VALUES
('111111','2011/01/12'),
('111111','2011/05/2'),
('111111','2012/3/16')

DECLARE @Table2 TABLE (ESN Nvarchar(35), Receiptdate datetime, supplierid int)

INSERT @Table2
VALUES
('111111','2011/02/15',45),
('111111','2011/06/25',45),[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-20 : 10:03:15

Just change Left Outer Join to Inner Join, and you will get only those records from table1 that have ESN in table2.

Jim

Everyday I learn something that somebody else already knew

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-20 : 21:07:48

@Jimf..Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 21:32:14

even then i dont think your record count will decrease as the relationship between tables seem to be one to many. Could you explain us what you expect as output when you try to return one record per ESN? as you see you've multiples per ESN in table 2 so if you want to return only one out of them you will need to specify rules to select the unique record per ESN

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-21 : 04:31:04

Hi Visakh,

Actually there's a late changes in my requirements w/c needed additional info.
Need to get the max date, if returndate is greather than receiptdate i will get desired supplier. There are the same ESN but with diff supplieriD, thats why i could not use joining the ESN becasue of this issue.

While trying my script, i could not get the exact result which i get duplicated entries.
for this example, in Sample1 table I have 5 records with the same ESN and diff returndate,
and for sample2 table i had 3 records with the same ESN with diff supplierid .
Runnning my Script i got incorrect data, all the ESN both from Sample1 and Sample2 table was displayed.


Data:


Create table #Sample1
(ESN nvarchar(35), Returndate datetime, ShipReturnType int)
Insert Into #Sample1
Select '001700001829830','2010-07-27 00:00:00.000',7
Union all
Select '001700001829830','2011-05-23 00:00:00.000',7
union all
Select '001700001829830','2011-12-08 00:00:00.000',2
union all
Select '001700001829830','2012-03-08 00:00:00.000',2
union all
Select '001700001829830','2012-05-17 00:00:00.000',4


Create table #Sample2
(ESN nvarchar(35), Receiptdate datetime, SupplierID int)
Insert Into #Sample2
Select '001700001829830','2010-06-16 18:25:04.447',44
Union all
Select '001700001829830','2010-11-06 08:46:51.127',43
Union all
Select '001700001829830','2011-11-05 06:37:24.707',43


SCRIPT:
Select
	us.ESN,
	max(asia.Receiptdate) as Receiptdate,
	us.ReturnDate,
	us.ShipReturnType,
    asia.SupplierID
--into #Data2
from Sample1 us 
Left Outer Join Sample2 asia 
	on us.esn = asia.esn
	and us.ReturnDate  > asia.ReceiptDate   
where us.ReturnDate >= '01/01/11' and  us.ReturnDate < '06/01/12'
  and us.ESN='001700001829830'
group by 
	us.ESN,
	us.ReturnDate,
	us.ShipReturnType,
    asia.SupplierID
Order by us.esn




BTW, 

What if i would like to get the max receiptdate from sample2.
here is my script but does not working properly.any idea. thanks.

Select
	us.ESN,
	max(asia.Receiptdate) as Receiptdate,
	us.ReturnDate,
	us.ShipReturnType,
    asia.SupplierID
from #Sample1 us 
Left Outer Join (select max(Receiptdate) as Receiptdate, ESN, supplierid
        From #Sample2 group by ESN, SupplierdID order by Receiptdate Desc) as Asia   
On asia.esn=us.en
where us.ReturnDate >= '01/01/11' and  us.ReturnDate < '06/01/12'
  and us.ESN='001700001829830'

Thanks,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:58:26

so what should be your required output?

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-21 : 19:27:27

Something like this:


ESN----------------------Receiptdate--------returndate------Shipreturntype--SupplierID
001700001829830—2010-11-06---2011-05-23------7--------43
001700001829830—2010-11-06---2011-12-08------7--------43
001700001829830—2011-11-05---2012-03-08------2--------43
001700001829830—2011-11-05---2012-05-17------4--------43

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 19:53:39

why the record with SupplierID 44 got ignored?

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-21 : 21:14:51

Let we say the 44 with fall under this ESN. what should be the query.
001700001829830—2010-11-06---2011-05-23------7--44..

while trying my query, i get in correct records. there are duplicated records.

Select
us.ESN,
max(asia.Receiptdate) as Receiptdate,
us.ReturnDate,
us.ShipReturnType,
asia.SupplierID
--into #Data2
from #Sample1 us
Left Outer Join #Sample2 asia
on us.esn = asia.esn
and us.ReturnDate > asia.ReceiptDate
where us.ReturnDate >= '01/01/11' and us.ReturnDate < '06/01/12'
and us.ESN='001700001829830'
group by
us.ESN,
us.ReturnDate,
us.ShipReturnType,
asia.SupplierID
Order by us.esn

derived result from this query:

ESN Receiptdate ReturnDate ShipReturnType SupplierID
001700001829830 2010-11-06 08:46:51.127 2011-05-23 00:00:00.000 7 43
001700001829830 2010-06-16 18:25:04.447 2011-05-23 00:00:00.000 7 44
001700001829830 2011-11-05 06:37:24.707 2011-12-08 00:00:00.000 2 43
001700001829830 2010-06-16 18:25:04.447 2011-12-08 00:00:00.000 2 44
001700001829830 2011-11-05 06:37:24.707 2012-03-08 00:00:00.000 2 43
001700001829830 2010-06-16 18:25:04.447 2012-03-08 00:00:00.000 2 44
001700001829830 2011-11-05 06:37:24.707 2012-05-17 00:00:00.000 4 43
001700001829830 2010-06-16 18:25:04.447 2012-05-17 00:00:00.000 4 44

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 22:01:58

unless you post proper data with required output I dont think anybody will be able to help you out!

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-21 : 22:26:49

Anyway, i would like to try concept.
I would like to get the max receiptdate from sample2 by ESN but i did not arrived the correct data or result.
Can yu please modify my script. Thanks.

here is my script.

Select
us.ESN,
asia.Receiptdate,
us.ReturnDate,
us.ShipReturnType,
asia.SupplierID
--into #Data2
from #Sample1 us
Outer Apply (Select top 1 Receiptdate, ESN, supplierID
from #Sample2
Where ESN='001700406158830'
Order by receiptdate desc
) as Asia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 22:42:06

I can keep on guessing unless you show me what exactly you're after!


Select
us.ESN,
asia.Receiptdate,
us.ReturnDate,
us.ShipReturnType,
asia.SupplierID
--into #Data2
from #Sample1 us 
Outer Apply (Select top 1 Receiptdate, ESN, supplierID 
from #Sample2 
Where ESN=us.ESN
Order by receiptdate desc
) as Asia

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-21 : 22:56:25

Yes, I think my requirements and data is not clear.
I will ask from the user waht is the exact requirements.

Thanks again Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 00:58:02

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources