How to get related rows

Using the query below, In addition to nearest price, I'd like to get all rows that have the same date.  The data might look like:

ID  tDate         price
1  11/1/2008   5.00
2  11/1/2008   7.50
3  11/3/2008   15.00
4  11/3/2008   14.00

If I run the query and substitute 6.00 for 100.00, I want to get row IDs 1 & 2.   1 because it matches the nearest price and 2 because it has the same date.  There could be several other rows with the same date.  What query will produce those types of results?
select top 1 price from
(
	select MAX(price) as price,(100.00 - max(price)) AS Diff from TableName where price <= 100.00
	UNION ALL
	select MIN(price) as price,(MIN(price)-100.00) AS Diff from TableName where price >= 100.00
 ) test 
order by Diff asc

Open in new window

brettrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BinuthCommented:

select B.* from TableName A
inner join TableName B ON A.tDate = B.tDate
where 
A.price =(
	select top 1 price from
	(
        select MAX(price) as price,(13 - max(price)) AS Diff from TableName where price <= 13
        UNION ALL
        select MIN(price) as price,(MIN(price)-13) AS Diff from TableName where price >= 13
	) test 
	order by Diff asc)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
Check this:

select a.id,a.tDate,a.price from tablename a
join
(select top 1 price from
(
      select MAX(price) as price,(6.00 - max(price)) AS Diff from TableName where price <= 100.00
      UNION ALL
      select MIN(price) as price,(MIN(price)-6.00) AS Diff from TableName where price >= 100.00
 ) test
order by Diff asc) b
on a.price = b.price
join tablename c
on a.tDate = c.tDate
0
SharathData EngineerCommented:
Updated everywhere 6.00

select a.id,a.tDate,a.price from tablename a
join
(select top 1 price from
(
      select MAX(price) as price,(6.00 - max(price)) AS Diff from TableName where price <= 6.00
      UNION ALL
      select MIN(price) as price,(MIN(price)-6.00) AS Diff from TableName where price >= 6.00
 ) test
order by Diff asc) b
on a.price = b.price
join tablename c
on a.tDate = c.tDate
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

brettrAuthor Commented:
Thanks.  Is one better than the other in regards to performance?
0
brettrAuthor Commented:
Actually, I was to quick on assigning solutions since Binuth's only gets prices equal to or less than and Sharath_123 doesn't return anything.
0
SharathData EngineerCommented:
Assume that you have data like below.
ID  tDate         price
1  11/1/2008   5.00
2  11/2/2008   7.00
3  11/1/2008   15.00
4  11/2/2008   14.00

Check whether Binuth's solution work for you. As per your requirement it should display the records with nearest prices. In this case records 1 and 2 both having nearest price (price 5 and 7). Then the records with same Dates (records having dates 11/1/2008 and 11/2/2208). In this case records 3 & 4.
The result set should be records 1,2,3 & 4.
Are you able to get this result set with Binuth's or anyone's solution here?
0
SharathData EngineerCommented:
brettr - dont jump on hitting accept button. Test any solution before doing that. because people generally check in the accepted solutions first if they encounter any problem. If they come to know that the solution provided is not working, thats not good for EE.
It may be bad impression that EE won't provide good solutions.
0
brettrAuthor Commented:
One or the other price will be chosen.  Say SQL Server chooses 5.  Then rows 1 & 3 should return.  If 7, then 2 & 4.

I'm able to get Binuth's solution going. The problem was more my modifications of trying to do a join with another table.  Thanks.
0
SharathData EngineerCommented:
My understanding is its not either 5 or 7. It should be both 5 and 7. The query should be written in such a way that it should return both 5 and 7 as both are nearest prices.
0
brettrAuthor Commented:
There is a problem with order by asc.  Since the UNION ALL returns tWo results, one may be NULL.  In this case the answer is NULL with a SELECT 1.  That's incorrect.  It should return the numeric from the other result.   You can get this by going with orderby desc.  However, now when you get numerics for both results, you send the wrong one on SELECT 1.
0
SharathData EngineerCommented:
So whats the conclusion? Do you think you want another solution for this kind of scenario?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.