Solved

Range of a field depending in a related table ms sql 2995

Posted on 2011-03-13
3
219 Views
Last Modified: 2012-05-11
I have a table a with numeric values

1
3
5
6
12
34
54
16

And another
0
10
20
30
40
I need a query to give me in wich ranga the values of table 1 fall.

for example, 6 relates to record 1 (0<=6<10) 31 relates to record 4 (30<=31<40)
0
Comment
Question by:robrodp
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 250 total points
ID: 35125362
replace Table1, fieldname1, Table2, fieldname2 with your table name and fieldname accordingly.

Declare @temp TABLE (rid int IDENTITY(1,1) PRIMARY KEY, data int)
INSERT INTO @temp (data)
SELECT fieldname2 FROM Table2
ORDER BY fieldname2

SELECT T.fieldname1, A.rid, A.Data, B.Data
FROM
	@temp A
	INNER JOIN @temp B ON A.rid + 1 = B.rid
	INNER JOIN Table1 T ON T.fieldname1 >= A.Data AND T.fieldname1 < B.Data

Open in new window

0
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 250 total points
ID: 35125427
SELECT t1.*, range = (SELECT MAX(t2.fieldname) FROM tableB t2 WHERE t1.fieldname>=t2.fieldname)
FROM tableA t1

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35125432
If you need the whole record from tableB, you can use outer apply
SELECT t1.*, t2.*
FROM tableA t1 OUTER APPLY
    (SELECT TOP 1 *
       FROM tableB
       WHERE t1.fieldname>=fieldname
       ORDER BY fieldname DESC
     ) t2

Open in new window

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now