SQL AND statement, avoid using between in the statement

Question

Hi, is there a way I can avoid using between in SQL statement and replace that with AND statement ?

Below is my query

Select * from [dbo].[TestData] Where [ID] between (('PINNum'*10) + 1) AND  ('PINNum'+1)*10'

What I am trying to get : I want to achieve the above logic through AND statement . Please advice how can I acheive this ?

Select * from [dbo].[TestData] Where [ID] = ((@PINNum*12) + 1)
AND 
(Select * from [dbo].[TestData] Where [ID] =  (@PINNum+1)*12)

Answer 1

Hi @Rock Hitman ,

A small modification on Viorel-1's query and please refer below:

 select * from [dbo].[TestData] Where ([ID]-1) /12 =@PINNum  

Equals to

 Select * from [dbo].[TestData] Where [ID] between ((@PINNum*12) + 1) AND  (@PINNum+1)*12  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What I am trying to get : I want to achieve the above logic through AND statement . Please advice how can I acheive this ?

Select * from [dbo].[TestData] Where [ID] = ((@PINNum*12) + 1)
AND 
(Select * from [dbo].[TestData] Where [ID] =  (@PINNum+1)*12)

To mean it sounds like you are asking for:

 Select * from [dbo].[TestData] Where [ID] = ((@PINNum*12) + 1)
 UNION ALL
 (Select * from [dbo].[TestData] Where [ID] =  (@PINNum+1)*12)

Not that it would give the same result as the query with BETWEEN, but you wanted to get rid of it...

In case you haven't noticed, this thread is a guessing game. That is, we are try to guess what you are really asking for. It would be a lot easier if you had posted CREATE TABLE statement for your able, some sample data with INSERT statements and the desired result given the sample.

Answer 3

Hi @Rock Hitman ,

You could also try with below:

 SELECT * from [dbo].[TestData]  
ORDER BY ID OFFSET @PINNum*12 ROWS FETCH NEXT 12 ROWS ONLY;  

Equals to:

Select * from [dbo].[TestData] Where [ID] between ((@PINNum*12) + 1) AND  (@PINNum+1)*12  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.