CODEWITHSUNDEEP
sql-server
Dekso
Dekso

Reputation: 561

Compare if 2 input numbers are in range of 2 columns

I have the ff table.

Series

startno | endno
1       | 99

and 2 inputs

DECLARE @inputstart int = 2, @inputend int = 100

I want to select where my 2 inputs lies in range of the ones in the table.

Upvotes: 0

Views: 49

Answers (3)

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

Suppose We have Created a temp Table 

CREATE TABLE #DeleteMe(startNo INT , endNo INT )

then we are inserting all the records in temp table For e.g. 1 to 100

;WITH nums AS
   (SELECT 1 AS startNo,1 AS endNo  
    UNION ALL
    SELECT startNo + 1 AS startNo,endNo + 1 AS endNo
    FROM nums
    WHERE nums.startNo <= 99)

INSERT INTO #DeleteMe
SELECT *
FROM nums

/* Here is Start Main Part that you want.... */

DECLARE @inputstart as  int ,@inputend AS INT
SET @inputstart= 2 
SET  @inputend = 50

SELECT * FROM #DeleteMe WHERE 
convert(INT,startNo)   BETWEEN Convert(INT,CASE WHEN isnull(@inputstart,'')='' THEN startNo ELSE isnull(@inputstart,'') END)                     
AND Convert(INT, CASE WHEN isnull(@inputend,'')='' THEN endNo ELSE isnull(@inputend,'') END)

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

Try this.

SELECT * 
FROM Your_Table 
WHERE (StartNo BETWEEN @inputstart AND @inputend) OR (endno BETWEEN @inputstart AND @inputend)

If you want in which column lies between your inputs, then try it.

SELECT MAX(CASE WHEN StartNo BETWEEN @inputstart AND @inputend THEN StartNo 
                WHEN endno BETWEEN @inputstart AND @inputend THEN endno END)
FROM Your_Table

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13026

try this

declare @inputstart int = 2, @inputend int = 100;

with cte as (
select 1 as startno, 99 as endno
) select * from cte where @inputstart between startno and endno
    and @inputend between startno and endno

Upvotes: 0

Related Questions