CODEWITHSUNDEEP
sqlsql-server
prakash s
prakash s

Reputation: 33

How to get in which criteria the range falls using sql query?

Code:

IF OBJECT_ID('tempdb..#TempMaster','U') IS NOT NULL
DROP TABLE #TempMaster

IF OBJECT_ID('tempdb..#TempTransaction','U') IS NOT NULL
DROP TABLE #TempTransaction

CREATE TABLE #TempMaster
(
Sno INT IDENTITY(1,1),
RangeDesc VARCHAR(100),
RangeFromValue INT,
RangeToValue INT
)

CREATE TABLE #TempTransaction
(
Sno INT IDENTITY(1,1),
[Values] INT
)

INSERT INTO #TempMaster(RangeDesc,RangeFromValue,RangeToValue)
SELECT * FROM (VALUES('Type A',1,10),('Type B',11,20),('Type C',21,30)) AS T(RangeDesc,RangeFromValue,RangeToValue)



INSERT INTO #TempTransaction([Values])
SELECT 1
UNION ALL
SELECT 15
UNION ALL
SELECT 5



SELECT * FROM #TempMaster
SELECT * FROM #TempTransaction

Please anyone help me to get a this kind of a solution based on which range the value falls. I want to get a range description and write down a query for a below output. Thanks in advance. 

-------------------------------
sno     Values      RangeDesc
-------------------------------
1       1           Type A
2       15          Type B
3       5           Type A
-------------------------------

Upvotes: 0

Views: 88

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

You do left join based on range values.

select t1.Sno, t1.[Values], t2.RangeDesc
from #TempTransaction t1
left join #TempMaster  t2 on t1.[Values] between t2.RangeFromValue and t2.RangeToValue

Upvotes: 1

Related Questions