June 21, 2012 at 6:14 am
Hi FOrumer's
I have my query to get the min date from sample2 table using the ESN andreturndate as reference and display or get the supplierid , unfortunately i could not get the correct records.
can you please help me guys. thanks in advance.
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
June 21, 2012 at 6:22 am
What should be the outcome of the query?
June 21, 2012 at 6:49 am
Thanks for the reply.
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
Another Quetion:
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'
June 21, 2012 at 7:54 pm
I have two questions:
1. According to your #Sample1 data, the ShipReturnType for this expected output row should be 2 and not 7 (please confirm).
001700001829830—2010-11-06---2011-12-08------7--------43
2. I don't understand how you're matching the 4 #Sample1 records returned when you consider the date range to the 3 #Sample2 records. Clearly you must want to apply the same date range to #Sample2, thus excluding the following record:
Select '001700001829830','2010-06-16 18:25:04.447',44
I just don't see how the MIN and MAX dates are matched back to the #Sample1 records. With that information, I believe a correct solution can be suggested.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply